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

#### 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.

1. powerpivotpro says:

Yep. And if you put StoreName on a slicer and performed a multi-select, you’ll get similarly “weird” results. You are jumping ahead to Thursday’s post… which is of course 100% expected 🙂

1. Colin Banfield says:

Not to mention that if you want to collapse the entire store level to show regions only, you end up with an empty table, or if you collapse the stores in any region, the region disappears from the table. I have no doubt though, that your Thursday post will address these issues.

2. Ram says:

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

REPORT EXPECTED RESULT:

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