I'm learning DAX.
For a measure, I can write: Profit = SUM(Sales[SalesAmt])-SUM(Sales[ProdcutionCost])
This gives me the Profit.
I get the same result if I do: Profit = SUMX(Sales,Sales[SalesAmt]-Sales[ProdcutionCost])
So what is the use case or difference between SUM and SUMX?
Actually, SUM
is just syntactic sugar for simple sums of a column.
SUM ( 'Table'[Column] )
is shorthand for (and will be translated to by the engine at query time)
SUMX ( 'Table' , 'Table'[Column] )
and the use case is, again, for when you only want a simple sum. If you want to do more things in a row context across your table, you need to invoke SUMX
.
Consider a case where you have a table like this, with unit price and quantity and want to calculate the total revenue:
Transaction ID | Product ID | Unit Price | Quantity |
---|---|---|---|
1 | 1 | 5.99 | 5 |
1 | 2 | 10.49 | 3 |
2 | 1 | 5.99 | 3 |
In this case you cannot do:
Total Revenue = SUM ( 'Table'[Unit Price] ) * SUM ( 'Table'[Quantity] )
Instead you would have to use the row context in the explicit SUMX
iterator to sum row by row:
Total Revenue =
SUMX (
'Table' ,
'Table'[Unit Price] * 'Table'[Quantity]
)