SUM
Adds up every number in one column.
SUM(<column>)Returns: A number (a scalar). BLANK when the column has no numeric values in the current filter context.
What it does
SUM is the most common measure in a model. It adds the values in one numeric column after every filter from slicers, rows, columns and page filters has been applied. The same measure therefore shows a different total in every cell of a matrix. Use SUM when the total is a plain column such as sales dollars, cost or units. When each row needs a calculation first, use SUMX.
Syntax and arguments
- column: a column reference such as
Sales[SalesAmount]. It must be a column. An expression such asSales[SalesAmount] - Sales[TotalCost]is not allowed.
Example
The controller wants sales, cost and gross margin on one dashboard, filtered by a date slicer.
Total Sales = SUM(Sales[SalesAmount])
Total Cost = SUM(Sales[TotalCost])
Gross Margin = [Total Sales] - [Total Cost]
Each measure adds its column in whatever context the visual provides. A slicer set to one fiscal year changes every number together. The margin is built from two sums, not by summing a margin column, so the result stays correct at every level of a matrix.
Example: a second, different use
Units shipped by product category:
Units Sold = SUM(Sales[SalesQuantity])
With Product[ProductCategory] on the rows of a matrix, each category shows its own unit total, and the grand total is the sum of the category rows.
Things to watch
- SUM accepts only a column. Writing
SUM(Sales[SalesAmount] - Sales[TotalCost])fails. Use SUMX for that. - Blank values are ignored. If nothing is left to add, the result is BLANK, not zero. Wrap it in COALESCE when a card must show 0.
- Never sum a ratio column such as Margin %. Compute the ratio from sums with DIVIDE.
- A column stored as text does not add up correctly. Check the data type in the model first.
- SUM does not respect row by row logic. If a value depends on the price or cost on each row, use SUMX.
In business terms
Totals for revenue, cost, units, hours and payroll sit on almost every finance report. SUM is the base of month-end totals, budget totals and the numerator and denominator of most margin and variance measures.