List.Sum
Adds up all the numbers in a list.
List.Sum(list, optional precision)Returns: A number, the total of the list.
What it does
List.Sum returns the total of a list of numbers. It is the M equivalent of SUM in Excel, and it works on any list, including a single column of a table. Use it for totals in a control check, a subtotal inside a query, or a quick figure to compare with the source system.
Syntax and arguments
list: the numbers to add. A table column is a list, soInvoices[amount]works.precision(optional): how the addition is done.Precision.Decimalis the usual choice for money.Precision.Doubleis the other choice, and uses floating-point arithmetic.
Example
An AR clerk wants the total of open invoices in one column of a bank-to-ledger check. The column is already a list.
let
Source = Excel.CurrentWorkbook(){[Name = "OpenInvoices"]}[Content],
OpenAmounts = Source[amount],
Total = List.Sum(OpenAmounts, Precision.Decimal)
in
Total
Total is one number. Compare it with the control total from the source system. If the two differ, look for blank or text values before you trust either figure.
Example: a second, different use
Totalling a list you built in the query: List.Sum({120.50, 80.25, 300}) returns 500.75.
Things to watch
- A text value such as "1,250" is not a number, so the sum fails. Convert the column first.
- Nulls are not zero in every case. Remove them with List.RemoveNulls when you want a clear result.
- Totals in a query may not match a pivot table if the pivot uses a different rounding or filter. Reconcile the figures.
- Sum in the source database when the data is large, so only the total crosses the network.
In business terms
Control totals, batch totals, subtotals for a reconciliation and the grand total on a schedule of receivables all start with a sum. A total that is checked against the source is the main evidence that a query is complete.