The Flag Appears at Top Level (Accessories), Telling You There’s a Problem Further Down.
Expand Accessories and You See the Culprit is Bottles and Cages (Specifically Road Bottle Cage)
Start With a “Sara Problem” Measure!
Let’s say you have a measure. Doesn’t matter what it is or how it’s calculated really, except that it reports on whether there’s a problem. It returns 1 (or Yes or True) if there’s a problem, and 0 (or No/False) if there isn’t. Or maybe it returns a “regular” number, but when it crosses a certain line, your business has decided that is Bad.
Get it? The measure is named “Sara Problem!!!!” To pronounce it properly you have to add a question mark – so technically, it’s named… “Sara Problem?”
Great pun! But I can’t take credit for it. Back when we lived in Seattle, my wife (girlfriend at the time, and fellow Microsoft engineer) played roller derby. She was a “Rat City Rollergirl,” she played for a team called Grave Danger, and her skater name was Natalie Fatality.
I am not making this up. Well, she had a teammate whose skater name was Sara Problem.
“Sara Problem” on Left, “Natalie Fatality” (Official Wife of PowerPivotPro) on Right
Back to the Formulas!
In my case, the story of Sara Problem starts with a measure named [1 Year Sales Trend].
The formula for [1 Year Sales Trend] is 100% irrelevant! Your situation will be different. Just know that it measures “year over year” sales growth or decline. So it goes as low as –100% (in case of products etc. that we haven’t sold at all this year) and can go as high as, well, there is no upper limit I guess.
But we’ve decided that anything that falls below –10% is Bad. So products that have declined by 10% or more, in other words – we want to flag those.
You can see [1 Year Sales Trend] displayed in the original screenshot from above:
Our 1 Year Sales Trend Measure, Highlighted
Take Note! Sara Problem is Very Meticulous!
At the Accessories Level the Trend is VERY Positive, but it’s Still Flagged!
Aha! So how is that second column of the pivot… done? Well it starts with another measure:
[Products in Decline] =
COUNTROWS(FILTER(Products, [1 Year Sales Trend]<-.1))
In English, that formula counts the number of rows in the Products table (each of which is an individual product) for which [1 Year Sales Trend] is below the threshold of –10%.
OK, so then how is the Conditional Formatting done?
The CF Rule is Pretty Simple: It Just Flags Cases Where There’s at Least One “Bad” Product
(Note the “Show Icon Only” Checkbox is Checked)
So we get a flag whenever there’s at least one “bad” product, no matter what level of the pivot we are at!
Of course, maybe you don’t want to count Bad Products. Maybe you want to count Bad Stores. Or Bad Customers. Or instances of Bad English. (Wait, that last one is something else).
No worries, you just change your second measure to count rows of the proper table. Or maybe even distinct values of a column, using VALUES(column). Knock yourself out.
But no one knocks people out like Sara Problem.