SUMX vs SUM – key differences very briefly (DAX – Power Pivot, Power BI)
This article describes the principal differences between the SUM and SUMX functions.
Let´s use this table with unit prices and pieces. We are going to calculate total revenue (unit price * pieces) split by items.
So, for example, for Vanila ice cream, the result is going to be 2 880 (= 12 * 110 + 13 * 120).
Should we use SUM or SUMX in this case?
How SUM works?
The measure with SUM could be like this:
- SUM total price = SUM('Table'[Pieces])*SUM('Table'[Unit price])
It is wrong. Why? Because SUM starts with summarizing of first column and second column, and then multiplies the results.
Let´s do it with SUMX. The syntax is like this:
- SUMX total price = SUMX('Table';'Table'[Pieces]*'Table'[Unit price])
It is correct. Because SUMX works row by row. Each row is calculated separately, and then the results are summarized.
That’s a simple but really clear explanation.
That’s a simple but really clear explanation too.