SUMX – summary with filter or additional calculation (DAX – Power Pivot, Power BI)

This article is about SUMX, which can be used in DAX. It is very handful when we need to make some calculation “before” summary or calculate selected rows only.

This article is about SUMX, but other “X-function” such as COUNTAX, COUNTX, AVERAGEX, MINX, MAXX work similarly.

Let´s play with this table containing a list of cars and demonstrate three basic tasks, that can be nicely handled using SUMX.

Summary with simple calculation

To summarize the total price of all cars, we could use SUM simply. But how to summarize the price, if every car was discounted by 10 000?

Theoretically, we could create a new column with difference, and then summarize it.

But it is much easier with SUMX:

• Discounted price = sumx(cars;cars[Price]-10000)

First argument declares that we want to work with “cars” table. The second argument says that the price will be discounted by 10 000 – this action has to be done with every row prior summary.

Summary with a nested function

Let´s say we are not going to discount all cars, but only cars with price higher then 200 000. We will use the IF function.

• Discounted price = sumx(cars;if(cars[Price]>200000;cars[Price]-10000;cars[Price]))

The first argument is a table again, The second argument says what has to be done with all rows – using a functon.

Table filtering

Now let´s work with the first argument. We want to summarize the price of red cars only.

• Discounted price = sumx(filter(cars;cars[Color]=”red”);cars[Price])

This could be combined with the previous steps – we can work with first and second argument independently.