Power Pivot Allows Some Amazing Stuff, Like Exception Flags that "Bubble Up!"

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!

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.

This has nothing to do with Power Pivot, or at least not directly.  But c'mon, it's badass :)

“Sara Problem” on Left, “Natalie Fatality” (Official Wife of P3 Adaptive ) 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:

Power Pivot Allows Some Amazing Stuff, Like Exception Flags that "Bubble Up!"

Our 1 Year Sales Trend Measure, Highlighted

Take Note!  Sara Problem is Very Meticulous!

Power Pivot Allows Some Amazing Stuff, Like Exception Flags that "Bubble Up!"

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?

Conditional formatting and Power Pivot make sweet love that even Barry White would respect.

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.