CODEWITHSUNDEEP
powerbidax
variable
variable

Reputation: 9684

What is the use case of SUM vs SUMX?

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?

Upvotes: 0

Views: 642

Answers (1)

Marcus
Marcus

Reputation: 4005

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]
    )

Upvotes: 4

Related Questions