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!
Can YOU Spot the Green or the Red Line? (Bonus Q: Can You Tell
That the Green Line is 50x higher than the Red Line?)
Look Ma! Two Posts!
After failing to maintain my Tuesday & Thursday posting schedule over the past two weeks (one post total, out of four scheduled), I’m BACK folks. I mean this week didn’t stand a chance. BAM! Nailed it.
OK, enough self-congratulation for meeting the minimum standards I set for myself. Moving on!
Three Different Scales
The three lines plotted on the chart above are “sourced” from these numbers:
These Three Measures Are VERY Different in their Relative “Sizes,”
Varying From 2 Digits to 6 Digits.
Data like that results in crappy charts. Let’s fix it with some formula magic:
Ah, Formulas Make Everything Better. Yep, it’s the same data, just “normalized.”
A Word from the Charting Pit of Derision!
Yeah, I hear that chittering out there – the mandibles of the Demonspawn Chart Fiends are clacking out a sound that resembles “Secondary Axis!”
Well I need THREE axes this time. Is there a Tertiary Axis feature, oh creatures of the dark? I mean, I seriously don’t even know. There ARE only two sides to the chart, so it would make sense I guess to NOT offer a tertiary axis. You’d have to start “stacking” scales side by side and that would probably make Tufte cry.
But I’m intentionally NOT checking whether there is such a feature. Because honestly I don’t even like the secondary axis feature that much.
“Oh did I break your concentration?” sayeth Jules?
Numbers are Numbers. Visuals are Visuals. Everyone has a preference.
There’s actually a deleted scene from Pulp Fiction where Mia asks Vincent if he’s a Beatles Man or an Elvis Man. The principle behind the question is that no one likes both of them, you either like one or the other. (Vincent, of course, is an Elvis man).
I have a similar theory – that you are either a Numbers Person or a Visuals Person. When you first get some new data, is your first instinct “I need to crunch this data with some formulas” or is it “I need to get this on a chart?”
Now, of course, a Numbers Person still uses charts. and a Visuals Person still sometimes needs to write some formulas. But which one is your first instinct – that determines which one you are.
In fact let’s just poll that out right now.
Anyway, I’m a Numbers Guy. Charts are very much a Last Step in the Process, if that, in my world. Heck, give me some conditional formatting in a pivot and I am usually set.
(Tellingly, though, I always NEED conditional formatting before I am happy. See, even a Numbers Guy can leverage visuals – it’s just that I am more on the numbers side of things.)
So, even in a case with TWO different measures, I am tempted to correct with formulas rather than track down the Secondary Axis feature hiding in its camouflaged lair.
I’m just not that comfortable with charts, really. I struggle to make them do what I want. And – I want to work with numbers damnit! So this is really all probably just personal preference on my part – I just camouflage it by saying things like “charts suck, gimme a formula and the numbers any day.”
Get on with it!
Goodness gracious I’ve been working on this post for 90 minutes and have not shown a single formula. I mean, polls and doctored photos of Vincent Vega on the throne don’t just make themselves you know! But geesh, a Numbers Guy should be more… Numeric. So it’s formula time.
Basically, we divide each measure by the maximum value of that measure, putting everything on a 0-100% scale. Here’s one of them:
[Sales Indexed to Max Week] =
[Total Sales Measure] /
MAXX(ALL(Calendar[WeekNumberOfYear]), [Total Sales Measure])
Note that Calendar[WeekNumberOfYear] is what I have on rows of my pivot (which “powers” the horizontal axis of my pivot chart). If you change the field on rows, you need to change that part of the formula.
The other two measures are exactly the same pattern, just substituting their respective base measures for [Total Sales Measure].
Here’s the pivot:
All three have been “normalized” to be between 0 and 100% – this yields the useful chart.
Alternative Formulas! Use the Average!
Maybe 0-100% is too restrictive for you. Maybe you want to divide by the average instead of the max.
OK, here goes:
[Sales Indexed to Average Week] =
[Total Sales Measure] /
CALCULATE([Total Sales Measure] /
Again, if you have something else on Rows/Axis, you need to replace Calendar[WeekNumberOfYear].
This yields the following, slightly different (better?) chart shape:
The Chart is Still Quite Readable, But the Fact that Active Customers Has Wider Variation
Than the Other Measures is no Longer Hidden by Being “Squashed” Into 0-100%
Of course, you could also just use AVERAGEX instead of those CALCULATE shenanigans in the denominator. In fact, I *did* do that for Transaction Size:
[Trans Size Indexed to Average Week] =
[Transaction Size] /
AVERAGEX(ALL(Calendar[WeekNumberOfYear]), [Transaction Size])
It doesn’t make a huge difference in most cases. AVERAGEX doesn’t “care” whether certain weeks had higher sales volumes than others – all weeks will be averaged as equals, whereas the fancy CALCULATE approach above computes the average as a grand total ratio, which is inherently weighted.
Anyway, Numbers and Visuals people. Til next time.