
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()
)
Read the rest of this entry »