A Common PowerPivot Trick:  Having a Measure Do Something Different on SubTotal and GrandTotal Cells

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:

    [Units Sold],

…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:

    [Units Sold],

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:

HASONEVALUE Gets Fooled by Some Cells

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

    [Units Sold],

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:

PowerPivot also Gives us ISFILTERED() Which Doesn't Get Fooled

ISFILTERED() is Not “Tricked” by the Nefarious Region 0 Smile

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 Smile