skip to Main Content

power pivot to power bi

A Power BI Technique Mined from the Power Pivot Archives

Below, you will find one of our all-time favorite Power BI techniques. In the ten (yes ten) years we’ve been operating this site, we’ve written over 1,000 articles on Power BI.  Hard to imagine - even for us - but true.

Years ago, we first wrote up this technique in the context of Power Pivot – Power BI’s cousin and predecessor, which we like to call “Power BI in Excel.” 

Since the two products share the same brains (DAX and M), this technique is more relevant today than ever. Everything below is 100% legit for Power BI – the same data model “shape,” the same DAX, etc. – but now you can use it to power up your Power BI visuals, and not just PivotTables.  Enjoy! Smile

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.



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 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 :=
    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”

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.

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

  8. Hi, as example take your picture(Simple SUM() does magic in Power Pivot: Define Once > Use Everywhere in action)
    How to calculate product between “germany” column and “france” column for all rows?

  9. this doesn’t help much, and I have looked all over the web for a solution.

    country 1961 1962 1963 CountryTotal
    USA 25 50 75 150
    China 15 25 50 90

    I am looking for the sumx formula in Power BI to add column countryTotal. I had the formula, then foolishly deleted the Power BI table without saving. This solution is found nowhere in the web, nowhere, even though the formula is ridiculously simple.

  10. Hi,

    I have created a new column with the formula sum( Quantity ) and it’s summarizing the quantity for all the records… is that expected?


  11. Author is simply awesome…..
    Explained well adding some funny stuff….

    How i never googled again to know what is SUM(0, SUMX() and Calculate() 🙂

  12. These are the type of tutorials that DAX needs. It should be for amateur/intermediate users as well as for the pros 🙂

  13. How can we calculate sum of a measure values which are filtered using comparison between two measures without using columns?

  14. I have a question. Doing a power pivot report for hours worked per day for employees. At the end of the row it will show the total hours (all good so far) but from that total, I need to determine how much OT was done? I created a measure but I can’t find a way to use the Total sum of hours in order to calculate OT. For example: total your for employee X = 50 and OT at 40. I would need to 50-40 =10 hours OT. But my measure seems to calculate OT per day instead to of the total(50 hours) at the end of the week. I tried SUMX but no luck.

  15. Rob (1/31/2019),

    Try this:
    Total OT = SUMX(TimeCard,
    If(TimeCard[Hours] – 8 > 0,
    TimeCard[Hours] – 8, Blank()
    If your Pivot Table is set with employee name and workdays on the rows, this measure will work on both employee total, and for each day. The table filters the workday by that day, but on the employee subtotal line it will iterate through all days of the week. Therefore you only need to do a check on 8 hours, not on 40. I would be interested to hear other solutions.

  16. Wonderful explanation for newbies like me. Very helpful because i can easily build on from here. What AVI has done here to explain the basics and not to delve into all situations because its not possible to do that..Situations are in their millions..Thanks AVI… Now i will read the Becoming one with CALCULATE to appreciate how the function works….

Leave a Reply

Your email address will not be published. Required fields are marked *