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)

Quick post today, continuing the saga of Anakin Skywalker.  An extension to the bubble up ranks technique.

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)

Wrapping Up

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.

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.

1. Kevin_L says:

Thanks for sharing — very cool solution that has potential to solve lots of similar situations — could be used to show stores that exceed plan or some other threshold – or identify demographic changes and so on…

1. powerpivotpro says:

Glad you like it Kevin. I really like this one, too. Can’t wait to encounter a real-world opportunity to use it, as opposed to just with the usual old sample data

1. Kevin_L says:

I could think of a lot of examples — this kind of stuff gets me excited You could weed out your profitable customers and/or orders for example.Try to increase the profitability of order and customers.

Another example might be if your were analyzing survey responses you might flag them with groups being a demographic…

2. Kevin_L says:

I can think of lots of examples.
Such customers that meet/exceed (or miss) a certain profitability
customers that meet/exceed (or miss) a certain profitability

or

Survey responses – — such demographics and certain parameter(s)

or given Obama care expenses to businesses — how they might impact them

2. Huw Jones says:

Hi Rob wonder if you could help – still very new to this. I am using this to show an issue with volume when it falls below a 5% threshold linked to it’s forecast volume.

Now it works for those values but it also shows a 1 for corresponding cell that has no value?

I have % remaining column that it runs against and if I add the 5% threshold to the pivot it also introduces rows with blank values. I’m assuming it sees it as a value for some reason, tried various things to filer this out but not found it yet.

Thanks

1. Huw Jones says:

I’ve managed to do a little more testing and the behavior occurs when I specify say less than 5%. As I have negative numbers it counts every row at that point and puts them in to the measure.

If I make it include all positive numbers or all negatives it works. Its when it crosses from positive to negative or negative to positive that all rows are counted.

I don’t think my puny newbie dax brain can work it out – (yet!)

If you do happen to read this and can offer any advice would be great.