powerbidax

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?


Solution

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