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?

SUM

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.

SUMX

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.

3 Comments

Comments are closed.