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

Rob Collie

One of the founding engineers behind Power Pivot during his 14-year career at Microsoft, and creator of the world’s first cloud Power Pivot service, Rob is one of the foremost authorities on self-service business intelligence and next-generation spreadsheet technology.

This Post Has 4 Comments

  1. I don’t want to be a wet blanket but, according to my tests, if you remove “Store 27” from the selection, it will change the result for all regions. HASONEFILTER() seems to do a better job.

  2. Rob – I have this DAX problem can you help? I am trying to summarize data (by Year) as shown below (line 1) under Expected results. I need to add up both contractedQty(fixed) and Actual Qty. How can I achieve this using DAX?

    DATA –
    contractnumber Year-Period material [QtyContracted For full Year] [ActaulQtyForPeriod]
    1234 2015-01 a 100 50
    1234 2015-04 a 100 40
    1235 2015-05 b 25 2
    4578 2015-01 a 600 200


    contractnumber Year material [QtyContracted For full Year] [ActaulQtyForPeriod]
    1234 2015 a 100 90
    1235 2015 b 25 2
    4578 2015 a 600 200

Leave a Comment or Question