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!
Post by Rob Collie
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!
PowerPivotPro.com 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
In 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
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:
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:
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!
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.
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]
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!
Added Weekend/Weekday on Rows, All Good
We Can Even Take the Original Values OFF the Pivot, And JUST See Pct!
Our Formula Keeps Working Even though Its Inputs are No Longer Displayed ANYWHERE
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.