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.