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!
Busy week here at the MVP Summit in Redmond. As usual, I can’t tell you anything I have learned this week – it’s strictly under NDA. But I have seen some really exciting things – at one point today I screamed out loud “YES!” in a packed room, and a little while later I said “I don’t think we can be stopped if we had something like this.” Where the “we” means us – me and you.
Enough of that. Here’s one from my archived list of “topics to cover on the blog at some point.”
Sales per Day Measure
Check out this relatively simple pivot:
Note that the subtotals for [Sales per Day] do not equal the sum of their parts. 2002’s total is $17,891 but if I add Mountain Bikes plus Road Bikes myself, I get a number closer to $19,000:
Totals don’t add up!
The formula for [Sales per Day] is:
[Sales per Day] =
[Sales] / COUNTROWS(DISTINCT(Sales[OrderDate]))
Which, in English, equates to “My Sales measure divided by the number of days for which I have sales transactions.”
Why Doesn’t it Add Up?
Well, if the “number of days for which I have sales transactions” is different for Mountain Bikes vs. Road Bikes, that will cause this problem. I can illustrate by adding that as a separate measure:
[Days I have Transactions] =
The Reason Why Sales per Day Doesn’t “Add Up”
So I only sold Mountain Bikes on 96 days in 2001, and Road Bikes on 180 days, but on 181 days, I sold at least one bike of any sort. This is why it doesn’t add up.
“Shouldn’t you divide by Calendar[Date] instead of Sales[OrderDate]?”
Some of you will no doubt have this question already: Isn’t it more accurate for [Sales per Day] to be based on the number of days in the calendar rather than the number of days on which it sold?”
And my answer is “probably, but it depends, AND this is a good example of a problem you WILL hit sooner or later in completely legitimate cases.”
For instance, if I opened my store in July 2001 and didn’t offer Mountain Bikes at all until September, I don’t want to divide either of my Sales amounts by 365, and I certainly don’t want to divide Mountain Bike sales by 180.
Even then though, I should probably have a separate table like Inventory or maybe [Start Date] and [End Date] columns in my Products table, and use those to create a measure named [Days Offered] measure, and use THAT as my denominator instead. That would be the fairest/most accurate approach.
But it would STILL have this same “doesn’t add up” problem. So let’s move on to a fix.
Forcing the Totals to Add Up
Let’s write a new measure:
[Sales per Day FIXED] =
[Sales per Day],
SUMX(VALUES(Category[Name]), [Sales per Day])
In English, this says
“If I am in a pivot cell that corresponds to a single product category like Mountain Bikes, then just use the normal [Sales per Day] measure. But if the pivot cell corresponds to more than 1 category, that means I am on a total, and then I want to have the total be the sum of all of the individual categories beneath it.”
For details on the whole “IF COUNTROWS” thing, see this post.
For details on SUMX, see this post.
Does it work? You bet:
The Second Measure Works
Pretty slick. There are shortcomings to this of course. If I put something other than Category on the pivot, the measure won’t work right. It is “tied” to the Category field. So it isn’t quite as portable as most measures, but it still is amazingly useful when you need it.