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!
The Blue Line Smooths Out Random Fluctuations, Tells a Less “Over-Reactive” Trend
I realized recently that this topic has never been covered before, in its most straightforward form, on this site! Actually, it was the subject of a guest post by the esteemed David Churchward, and also by the equally-esteemed Kasper de Jonge, but neither of those posts benefited from the v2 functions available to us today).
To illustrate what we can do with state-of-the-art Power Pivot formulas, let’s start with this simple model:
And a simple pivot:
That [Units Sold] measure is the jagged red line in the chart at the top of the post, and its formula is very simple:
[Units Sold] = SUM(Sales[QtySold])
And we want a version of [Units Sold] that is “smoothed” over a 3-month period.
Let’s start with a formula that is a sum of the most recent 3 months (including the current one):
[3 Month Moving Sum Units Sold] =
And see what that looks like:
Moving 3-Month Sum Reflects the Current Month and the Prior Two Months
Moving Average – First Attempt
OK, but that number is bigger than a single month and doesn’t match the scale of our real-world business, so we wouldn’t want to chart that – we want the average version of that.
It’s a 3-month moving sum, so to get the average, we could just divide by 3:
[3 Month Avg Divide 3] =
=[3 Month Moving Sum Units Sold] / 3
Which looks like:
3 Month Moving Avg Via Divide by 3 Has a Drawback
Those first two months, since they are the first two months in our calendar, are summing up less than 3 months’ worth of sales, but still dividing by 3. So it “unfairly” drives down their average.
Moving Average – Corrected
We can account for this by changing our denominator to use a similar logic to the numerator:
[3 Month Moving Avg Corrected]=
[3 Month Moving Sum Units Sold] /
In English: “take the 3 month sum measure we already have and divide it by the number of distinct (unique) months we have over that same 3 month period.”
This Calc is More “Fair” to the Months at the Beginning
There are a number of variations on this approach – daily/weekly/quarterly versions, correcting for calendars that extend beyond the range of dates where you have sales, adapting it to custom calendars via the Greatest Formula in the World, etc., but I will wait and see what people ask about in the comments before digging into any of those.