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.

Power Pivot Measures: Define Once, Use 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])

image
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.

Teacher Apples SUM 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_SeparateQuantityPrice

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.

Use Calculate to create your dream measure here

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.

  Subscribe to PowerPivotPro!
X

Subscribe

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. 

This Post Has 19 Comments

  1. 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. 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.

      To your questions
      > 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.

  2. 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?

  3. 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. 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]))

  4. 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 πŸ™‚

  5. 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. 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.

  6. 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

  7. Hi I am looking to create a running total of a calculated field based on what is being displayed in the pivot table. Is something along these lines possible?

    Currently the calculated field produced 19 age specific values in the pivot which is what I want. at the subtotal point however I require a sum of the 19 products created by the calculated field rather than what it currently does, being summing the 19 numerators/denominators and performing the calculation on those.

Leave a Comment or Question