DAX – Subtotals and Grand Totals That Add Up “Correctly”

 
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] =
COUNTROWS(DISTINCT(Sales[OrderDate]))

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] =
IF(COUNTROWS(VALUES(Category[Name]))=1,
   [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.