A Common Trick: Having a Measure Do Something Different on SubTotal and GrandTotal Cells
A Progression of Technique
It’s been awhile since I’ve written a good old honest formulas and techniques post, so I figured today would be a good time to “get my hands dirty” again.
Ever since PowerPivot v1, I’ve been writing measure formulas that “detect” whether the current pivot cell is a “total” cell (either a subtotal or grandtotal), and then doing something different if it is.
In the image above, my [Blank if Total] measure returns BLANK() for all totals, otherwise it returns the same value as the original [Units Sold] measure.
In PowerPivot v1, that formula would have looked like:
=IF(COUNTROWS(VALUES(Stores[StoreName]))=1,
[Units Sold],
BLANK()
)
…which basically says “if I have more than one value for StoreName, return BLANK(), but in cases where I have only one value, return the original measure.”
That was clumsy – a lot to write for a simple “do I have one value” test. So in PowerPivot v2, they gave us the HASONEVALUE() function which makes things cleaner:
=IF(HASONEVALUE(Stores[StoreName]),
[Units Sold],
BLANK()
)
Useful in Many Situations
Returning BLANK() for totals is just one application of the technique above. Here’s an old post where I use that technique, as well as a “branching measure” formula.
I also use it in cases like making totals add up correctly, which is one of the most popular posts on this site on an ongoing basis.
A Minor Flaw Though
“Having one value” and “not being a total cell” are not precisely the same thing. For example:
Region 0 Only Has One Store, So it “Tricks” Our Formula!
Get it? Region 0 only has one store – store 26. So, in the context of the Region 0 total cell, there’s still only one store! Our formula is fooled!
Sometimes, that won’t matter. But when it does matter, we need an alternate approach.
ISFILTERED() – An Alternative
=IF(ISFILTERED(Stores[StoreName]),
[Units Sold],
BLANK()
)
OK, ISFILTERED() is different. It explicitly checks to see if we’ve got a filter on StoreName in our current cell. And this fixes the flaw from above:
ISFILTERED() is Not “Tricked” by the Nefarious Region 0 
This is all either quite clear or quite confusing, depending on your mindset. Totals are really just the absence of filters – in the pivot above, all of the total cells have no filter on the [StoreName] field.
Most of the time, when you have an absence of a filter, you are going to have more than one value for that unfiltered field. Sometimes you don’t. ISFILTERED skips all of that and just asks “no really, is [StoreName] filtered right now?”
(The concept of total cells being the absence of filters is covered in pages 65-67 of the book, for those of you who want to reference it).
OK, it’s good to know we have a way to fix this. But there’s another function lingering out there – HASONEFILTER(), and my spider sense is tingling a bit that there’s more to cover here.
So I’ll loop back to this on Thursday
