DAX – SUM, SUMX or CALCULATE()…Choices, choices!

By Avichal Singh (Avi)

When I was working recently with a client, helping her remotely – I asked her to calculate the sum for sales amount in the table. She responded whether she should use SUM, SUMX or CALCULATE?

Simple question, but not a simple answer. Or I can give you the classic lawyer response – “It depends!” Let’s review.

SUM: Simple Unmitigated Magic

The good news is that a simple SUM, would work in majority of the cases. A simple sum in the hands of Power Pivot is a powerful tool. With the magic of relationships a simple SUM can show you tricks you could never have imagined in Excel.

Power Pivot relationships mean, that you define your measures once and use them everywhere.

“Define Once, Use Everywhere”

Your measures conform to the shape of your pivot, so you can drag and drop any fields from your model, use any slicers and the measures would still work.

Sales:=SUM(Transactions[Amount])

Simple SUM() does magic in Power Pivot: Define Once > Use Everywhere in action

Only when you see the results and go…”Uh…that is not exactly what I want”, should you explore other options.

Step at a time – SUMX

SUMX is an iterator. Unlike SUM which can operate on blocks of data and is very efficient, SUMX steps through your data one row at a time and is less efficient. Therefore look to use SUMX only when you cannot use SUM.

To use an analogy, if I asked my 10-year old son this question:
QUESTION: If I gave you 5 apples every day for 10 days how many apples would you have?

He would say “50” right away, since he quickly multiplied 5 x 10 to get his answer. That is SUM in action 🙂

If I asked my five-year-old daughter the same question it would take her some time. Because what she would need to do is: 5 + 5 + 5 + 5 + 5 + 5 + 5 + 5 + 5 + 5

Or to see it step by step:-
5 + 5 = 10
10 + 5 = 15
15 + 5 = 20
…
45 + 5 = 50

That is SUMX.

This picture explains nothing, but has my handsome likeness 🙂

So we should never use SUMX and always use SUM, right? Well, in some cases row by row iteration is exactly what you want.

Lets see a simple example. Say, in our transactions table, we are only storing the quantity sold and the unit price.

Transactions table only stored Quantity and Unit Price

If we were to write a measure to get the total sales amount, in this scenario we cannot use SUM. Since adding any column in bulk, would not give us the total sales amount. In this case we need to iterate row by row, in order to get our answer. SUMX to the rescue!

Sales :=
SUMX (
Transactions,
Transactions[Quantity] * Transactions[UnitPrice]
)

This would:-

1. iterate over the Transactions table, stepping row by row
2. calculate Transactions[Quantity] * Transactions[UnitPrice] at each row
3. In the end, sum all of them up to give us our Total Sales Amount

Note: Yes, you can use a calculated column, just weigh your options: When to Use Measures vs. Calc Columns

SUM wrapped in CALCULATE() – Would you like fries with that?

On cold days, you can choose to wrap your SUM inside a CALCULATE to keep it warm. SUM also loves marshmallows and hot chocolate. Treat it nice and it will serve you well.

You can see I have a soft corner in my heart for my DAX functions 🙂 Okay, enough of that.

Use CALCULATE when you need to change the filter context. Uh… What does that mean?

You will know the moment, when you look at your pivot and make a “but” statement.
”Yes I see the Sales Amount <or substitute your simple SUM measure here>)…
– but can I also see it for the last year”
– but can I just see it for the bikes category”
– but can I see it only for the weekday sales”
…etc.

To clarify, what you desire here, is not to change the filters on your existing pivot or the shape of the pivot. You want to keep your pivot the same, but still be able to display these additional “but” values.

This is the scenario that CALCULATE is built for. As an example we would take one of the scenarios and write the measure for that.

Sales for Bike Category :=
CALCULATE ( [Sales], DimProduct[ProductCategory] = “Bikes” )

I would not go further into the details of explaining the CALCULATE mechanism and filter context. There is plenty of material on this site to help you learn that (blog, book, online course, live class). But if you are just starting out with Power Pivot and DAX, or have been using this for a while: but still find yourself asking the question – “Should I use SUM, SUMX or CALCULATE?” hopefully this helps you answer that.