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.

#### Avi Singh

Avi Singh has personally experienced the transformation and empowerment that Power BI can bring - going from an Excel user to building large scale Power BI solutions. His mission now is to share the knowledge about Power Pivot and Power BI. He is based out of Seattle, WA.

## This Post Has 18 Comments

In your transaction table showing Quantity and UnitPrice columns, if the UnitPrice was on a separate ProductDim table, could you simply adjust the last part of the SUMX formula from Transactions[UnitPrice] to ProductDim[UnitPrice]? Also, if your transaction table happened to be 300 mil rows with a ProductDim table of 5,000 rows, I would think keeping this column on the ProductDim table would be more efficient than the larger transaction table (I do have the option to bring this column in on either table)…thoughts?

PS – you guys rock!

1. Avichal Singh says:

Yup, this was a bit of contrived example. So do not read too much into the way data is structured. If I could (using SQL Query or Power Query on Excel End) I would try to get the actual Sales Amount in my Transactions table.

> If you did have [UnitPrice] in a separate Products Table, the formula would change to
Sales :=SUMX (Transactions, Transactions[Quantity] * RELATED(DimProduct[UnitPrice]))

> Yes, fewer columns in your Data table the better. Keep it as narrow as you can.

Thanks Avi…that certainly helps.

2. Roxanne says:

I have five columns to add. How shall I write it as a sum formula?

2. PhilC says:

Nice simple explanation for us newbies, thanks for catering to all skill levels with the posts.

3. Richard says:

Excellent example, keep em coming.

4. Melissa says:

Avi, I use sum, sumx and calculate a lot in my measures, however, I’m finding that when I pull these measures into my power pivot, my drill down results are not reflecting the “filtered” data from my measure, it reflects the entire data table. How can I fix this?

5. I can only say: “I fell in love with this post” (and this blog). Thank you

6. Estevao Costa says:

Ok… what if you need to load the data model in your solution in 3 parts (pretty much like Jack would do with his victims).

I have a table named InvoiceData.
Then I have a table named Customer
Finally I have the table Currency.

The InvoiceData has the amount of invoices in different currencies and a Customer Code, but no Country name information. The Customer table has the Customer code that is used as relationship with the InvoiceData, but do not have the currency rate for that country. Finally, the Currency table has the currency information and the relationship with the Customer table.

So, in my tiny head, the formula that should do the trick of calculating my invoices on the exchange rate I want would be:

=SUMX(InvoiceData,InvoiceData[Gross Value]*Currency[Rate])

BUT! This cannot be done, because a error pops up, saying ” The Value for column ‘Rate’ in table ‘Currency’ cannot be determined in the current context…”

I believe this is happening because although my relationship works for everything else I try to do on this spreadsheet (InvoiceData[CustomerCode]>Customer[CustomerCode]>Customer[Country]>Country[Name]) power Pivot is unable to find out that which row to use to on the multiplication.

In short, it does not seems to know that Customer A is from Germany, and thus it should look at the Germany row on Currency table and look for the value on the Rate column to find out the amount in USD.

It does work if I use:

But once I try to do the same for other column on the InvoiceData, such as Net or Taxes, it raises the circular error. Which I also understands why it happens. I do not need to calculate NET and Taxes, but HEY! We did not electricity until we learned how many things we can do with it! So now I want to know how to do it right on this scenario.

I do can migrate the date to 2 spreadsheets… but I am stubborn and I want to do it on the hard way!

Any tips on that?

1. Estevao Costa says:

Forgot to add the formula I’m using to make it work… it is this one. I do not need the ALL part… that was me playing with it and trying to find solutions 😛

It does work if I use:=CALCULATE(SUM(InvoiceData[Gross Value])*SUM(Currency[Reverse]),ALL(InvoiceData[Gross Value]))

7. estgas says:

dear avi, any thanks this is a very helpful post! please consider to include in this same post also an explanation for the SUMMARIZE function 🙂

8. Gerrett van der Linde says:

i need to sum the m walked for only team 1
I did this =CALCULATE(SUM(M_Walked),FILTERS(Teams[TeId]))
But this returns all of the values “total of 423”
i need the following

Team 1 – day 1 – 100M Total 170
Team 2 – day 1 – 10M Total 43
Team 3 – day 1 – 90M Total 210
Team 1 – day 2 – 60M Total 170
Team 2 – day 2 – 13M Total 43
Team 3 – day 2 – 30M Total 210
Team 1 – day 3 – 10M Total 170
Team 2 – day 3 – 20M Total 43
Team 3 – day 3 – 90M Total 210

can this be done?

1. Thinzar Gyaw says:

1. i added your data to data model as 4 separate columns
Team Team2 Day Meters
Team 1 – day 1 – 100M Total 170 1 1 100
Team 2 – day 1 – 10M Total 43 2 1 10
Team 3 – day 1 – 90M Total 210 3 1 90
Team 1 – day 2 – 60M Total 170 1 2 60
Team 2 – day 2 – 13M Total 43 2 2 13
Team 3 – day 2 – 30M Total 210 3 2 30
Team 1 – day 3 – 10M Total 170 1 3 10
Team 2 – day 3 – 20M Total 43 2 3 20
Team 3 – day 3 – 90M Total 210 3 3 90
2. Name the table as TeamTable
3. Add a measure as : Subtotal:=CALCULATE( SUM( [Meter]), ALLEXCEPT(TeamTable, TeamTable[Team2]))
I found this formula from one of the forum.
Thanks for sharing cases.

9. Sandeep says:

Excellent examples added with some wonderful explanation, Thank you very much!

10. Najeeb says:

In my organisation we have 40 departments ,how i calculate the department wise cost in a calculated column

11. Theo says:

Hi, say you have 6 columns of data and u want to add them row by row or in some cases do standard deviation, what would be the formula? I tried sumx or stdevx.p but it resulted to errors. At one time it says my data should be scalar data.
Thanks for the help