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

Post by Rob Collie

Schrodinger's Cat Has Relevance to Power Pivot and Power BI

In the Classic Physics Thought Experiment, Schrodinger Hypothesized a Cat That Was Simultaneously Dead AND Alive.
(But here we will use the more humane “Simultaneously Green AND Grey.”

Back to Basics! celebrated its 5th birthday back in November.  Over 5+ years, we’ve progressively covered techniques with an increasing level of sophistication.  That’s pretty natural – we ourselves have become more skilled over time, AND there’s a tendency to not want to write the same post twice.

But today I want to drive home a basic point, one that will help “recruit” Excel pros into the Power BI camp, AND that will help “crystallize” a few things even for the longtime DAX practitioners.

Schrodinger’s Cat – A Classic Battle of the Nerds

imageIn 1935, physicist Erwin Schrodinger wanted to show Albert Einstein how wrong he was.  Einstein had recently published a paper that made an astounding claim about the nature of subatomic particles.  If those claims were true, said Schrodinger, even “big” everyday stuff, like cats, could also behave in that same outlandish way.  Which made Einstein look kinda silly, in Erwin’s mind.

He proposed the idea of a cat that was both simultaneously alive AND dead, and basically said “See, Albert?  Alive AND dead is clearly impossible, so your theory is junk.”  See here for details.

But modern quantum physicists actually think the cat experiment does NOT disprove Einstein’s claim.  In fact, they think Schrodinger’s Cat demonstrates that the universe is fundamentally a MUCH stranger place than we typically think.

In short, the concept of “impossible” is subject to re-evaluation.

This PivotTable is Simultaneously Filtered AND Unfiltered

Power Pivot Gives Us THIS - Filtered and Unfiltered Values Simultaneously, Where Each Column Respects a Different Filter, or Absence of a Filter

A Simple Little Picture Yes?  But it Hints at a Quantum Shift in Analytical Power.

“The pivot pictured above is impossible,” says Schrodinger.  Sorry, you just can’t do it.  Nope, no way.  The “Just Bike Sales” column is behaving as if the pivot is filtered to Bikes and the “Total Sales” column is behaving as if there’s no filter at all.

You CAN do that of course, but you can’t do it at the same time.  You have to add a report filter (or a slicer) and then keep toggling it back and forth:

You Keep Toggling that Report Filter Back and Forth on Your Pivot.  You're gonna hurt yourself.

Just Keep Toggling the Pivot’s Report Filter Back and Forth
Sometimes this is fine, sometimes it’s clumsy.

“Simultaneous” IS Possible with Two Pivots

Excel users are a clever sort, and sooner or later you realize we can just have two pivots:

Time for Two Pivots Side by Side

Have You Ever Done This – Two Pivots of the Same Shape, But With Different Filters Applied?
I Sure Have.

And then, the next logical step: in-grid formulas to calculate the ratio or difference between the pivots!

Formulas Referencing into Identical Pivots With Different Filters.  Oh the humanity.

You May Have Done This, Too.
(In this case I eschewed GETPIVOTDATA and used A1 ref, but have done it both ways –  a lot).

But Then, Humanity Throws a Wrench Into the Works

You’re all done.  You’ve built your multiple pivots, written your formulas, and present your results.

The first thing they say is “awesome, but we also need to see it broken out by X.”

You want to strangle “them.”  Don’t do it, but I understand why. 

Because when you add another field to the pivot, your in-grid formulas need to be re-written, and sometimes you need to completely re-organize your worksheet because the pivots, as they change size, start colliding with one another.

So you tell them no, not possible in the time allowed. Which is often the absolute truth – a simple change like that quite frequently is a “start from scratch” situation.

Be Like Einstein.  Use CALCULATE.

Power Pivot Gives Us THIS - Filtered and Unfiltered Values Simultaneously, Where Each Column Respects a Different Filter, or Absence of a Filter

This IS Possible, With Power Pivot and/or Power BI.

Just a simple little formula:

[Just Bike Sales]:=

CALCULATE([Total Sales], Products[Category]=”Bikes”)

One pivot, simultaneously behaving as if it’s filtered AND unfiltered – one column is, one column isn’t.

This is NOT possible with normal pivots.  OK sure, you can add a calculated column in the original source data that contains 0 unless a sale is a bike sale, and the full amount if it is, and then you put THAT column on your pivot.  But that takes forever, and litters your source data with dozens of columns over time.  You know you CAN do it, but you DON’T do it – at least not very often.

But then we pile on, add another formula, and we’re off to the races:

[Pct of Sales from Bikes]:=

[Just Bike Sales] / [Total Sales]

Ratios in a Pivot!  YES!

Our Formula for Percentage is Now in the Pivot!

We Can Rearrange the Pivot and the Formula “Fills Down” Automatically – Grows/Shrinks/Adjusts with the Pivot!

Formulas that Auto-Adjust With the Pivot, Fill Down, Grow/Shrink - Double YES!

Added Weekend/Weekday on Rows, All Good

We Can Even Take the Original Values OFF the Pivot, And JUST See Pct!

No Need to Even SEE the Underlying Values

Our Formula Keeps Working Even though Its Inputs are No Longer Displayed ANYWHERE

Rearrange, and the Power Pivot Formula Just Keeps Working

Rearrange?  Drag Country to Columns?  No Problem.

Schrodinger’s Head Explodes

Take a look at those last two pictures.  In those pivots, EACH CELL is behaving simultaneously as filtered and unfiltered.  It’s dividing the filtered amount by the unfiltered amount.

And honestly, this is one of the more MINOR examples of CALCULATE’s power.  We could be here for a long time if I tried to show an exhaustive list.

Since 2009 we’ve been saying CALCULATE is a supercharged SUMIF.  And Einstein would approve.

Rob Collie

One of the original engineering leaders behind Power BI and Power Pivot during his 14-year career at Microsoft, Rob Collie founded a consulting company in 2013 that is 100% devoted to “the new way forward” made possible by Power BI and its related technologies. Since 2013, PowerPivotPro has rapidly grown to become the leading firm in the industry, pioneering an agile, results-first methodology never before seen in the Business Intelligence space. A sought-after public speaker and author of the #1-selling Power BI book, Rob and his team would like to help you revolutionize your business and your career.

This Post Has 12 Comments
  1. Rob, what a great way to frame the power of CALCULATE() ! Simultaneously filtered and unfiltered. Supercharged SUMIF(). I think that is what drew us to your content initially and inspired us to step out and begin helping others. You continue to inspire Rob. Keep it up.

    When you add the CALCULATE feature with the ability to generate calculations from multiple tables (like actual and budget) with different granularities, and then FILTER() to change the context… well, it is kind of mind-blowing.

    Keep them coming Rob!

  2. Rob,
    Here is something strange about Filter / Calculate
    1. Define mSales:=CALCULATE(DATA[SALES])
    2. Define mPrevYear:=MIN(CAL[CAL_YEAR])-1
    3. Define
    mSalesPrevYear:=CALCULATE([mSales], FILTER(ALL(CAL),CAL([CAL_YEAR])=[mPrevYear]))
    Gives us Blanks
    4. Define
    CALCULATE([mSales], FILTER(ALL(CAL),CAL([CAL_YEAR])=MIN(CAL[CAL_YEAR])-1)) works as expected
    So Define once – use every where does not seem to work in all cases !

  3. Also in the above we could do previous year sales simply by using
    But it is still strange the way FILTER behaves the way it does

  4. Yes this is by far the #1 most arbitrary thing about DAX. The one time I think the DAX team perhaps made a mistake – which is an amazing thing really, to only have one “ouch” mistake in the whole language.

    CALCULATE() promotes row context into filter context. In a lookup table, try writing a calc column that sums up a column in a related data table. If you use =SUM(), you get the grand total of the entire data table. But if you do CALCULATE(SUM(…)), suddenly you get the sum of the related rows.

    So far so good, I’m ok with that.

    The real weird thing is that when you use a measure name, that silently IMPLIES a CALCULATE.

    So the row context becomes a filter context again.

    And within FILTER, each row that’s examined becomes its own row context.

    When we convert that row context into a filter context, we then look at a row and compare it TO ITSELF, rather than to all rows in the CAL table. And no row is equal to itself minus one. So we get blanks.

    But when we use just the “naked” MIN, we compare the currently-being-examined row to the MIN of all rows from the PIVOT’s filter context, which is what we wanted.

  5. @Rob – My head is spinning reading your explanation 🙂 – It would be really great if you could do a full blog post with “Green” Diagrams explaining this whole thing about the row context becoming a filter context again ! 🙂

    Now to go and read it again to see if I can understand it the second time around

    1. @Rob,
      I did some more “thinking” – It just got me confused further

      Lets assume you want to create a measure that shows growth since inception and you don’t want to hard code the first year

      1. Create a measure mFirstYear:= YEAR(FIRSTDATE(ALL(CAL[CAL_DT])))
      2. mSalesFirstYear:=CALCULATE([mSales],FILTER(ALL(CAL),CAL[CAL_YEAR]=[mFirstYear]))

      Works Fine !!!!

      3. mSalesGrowthInception:=DIVIDE([mSales]-[mSalesFirstYear],[mSalesFirstYear])

      3 mSalesFirstYear:=


      Also works fine

      Now I am comparing

      mSalesPrevYear:=CALCULATE([mSales], FILTER(ALL(CAL),CAL([CAL_YEAR])=[mPrevYear]))

      Where I put a measure in Filter Criteria of Filter and it messed up Calculates Filter context

      But it didn’t mess it up in mSalesFirstYear

      Now this would have confused Mr Schrodinger and Einstein and the Cats 🙂

      I definitely need a “green diagram” post to understand whats Calculate up to…

      1. mPrevYear:=MIN(CAL[CAL_YEAR])-1
        mFirstYear:= YEAR(FIRSTDATE(ALL(CAL[CAL_DT])))

        As Rob stated, when you use a measure name, that silently IMPLIES a CALCULATE.
        CALCULATE always transforms row context into filter context.

        However, due to the ALL-function, the second measure does not care about the additional filter context.

        Thus, there is no difference between





        mSalesPrevYear:=CALCULATE([mSales], FILTER(ALL(CAL),CAL([CAL_YEAR])=[mPrevYear]))


        mSalesPrevYear:=CALCULATE([mSales], FILTER(ALL(CAL),CAL([CAL_YEAR])=MIN(CAL[CAL_YEAR])-1))


  6. Sam, Rob,

    I was just going to write you Rob to tell you I could draft up a guest post about a pattern I use that is a good example of schoedinger filtered/unfiltered behaviour – it is quite similar to what Sam is using – so I have a calculated column in a table specified as:
    FILTER(DimYears, DimYears[Year]=RELATED(DimYears[SourceYear])))

    The table, obviously, has a year column which is related to Dim Years (also X is related to DimX and Y to DimY). What I am doing is I have some source data on imports of commodity X by sector Y, but not for all years. So I need to use the shares from some of my source years in some of my years with the data missing. At first I was using an IF formula, e.g.

    IF([Year]=”2008″, CALCULATE([Share_of_X_in_Y_for_Year_Z],
    FILTER(DimYears, DimYears[Year]=”2009″), [Share_of_X_in_Y_for_Year_Z])

    But then I needed to do it for more than one case, and I wanted the ability to try using different base years for the years with the missing data, so I added a column to my DimYears table that would allow me to specify for a given year which source year to use, e.g.

    Year SourceYear
    2008 2009
    2009 2009

    Then I used the formula above… and I was frankly quite surprised that it worked! It really quite boggles the mind… in order to find the related year, it has to know the current year for the row and relationship that has with DimDate, but then the filter simultaneously tells the function to ignore that relationship completely and manufacture a new relationship with DimDate

  7. How would you get combined Total Sales for both “bikes” + another criteria? lets say, “skateboards”

Leave a Comment or Question