In DAX, two functions seem to do the same thing but hide a fundamental difference: SUM and SUMX. Choosing the wrong one gives wrong totals — and worse, the error often goes unnoticed. Understanding when to use each is a milestone in mastering DAX.
SUM: adding up a column
SUM adds up the values of a single existing column. SUM(Sales[Amount]) returns the total of the Amount column. Simple and direct — when the number you want is already in a column, this is what you use.
SUMX: calculate row by row, then sum
SUMX is an "iterator": it goes through the table row by row, computes an expression on each, and only then sums it all. SUMX(Sales, Sales[Quantity] * Sales[Price]) multiplies quantity by price on each row and sums the results.
The difference that changes the result
The classic mistake is doing SUM(Quantity) * SUM(Price) to get total revenue. That multiplies two totals and gives a completely wrong number. The correct way is SUMX: multiply row by row and only then sum. The order of operations is everything.
When to use each
- SUM: when the value you want already exists in a column ready to sum.
- SUMX: when you need to compute something per row (multiply, condition) before summing.
The pattern applies to the whole X family
DAX has AVERAGEX, MAXX, MINX, COUNTX — all iterators with the same logic: compute row by row then aggregate. Understanding SUMX is understanding the whole family, one of the most important ideas in DAX.
In practice
Whenever you need a calculation involving more than one column per row, think SUMX, not SUM of separate columns. This care avoids wrong totals nobody catches until it is too late. Do your revenue calculations multiply row by row, or sum separate columns?
