skip to Main Content

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 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:

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.

Rob Collie

Rob Collie

One of the original engineering leaders behind Power BI and Power Pivot during his 14-year career at Microsoft, Rob Collie founded a consulting company in 2013 that is 100% devoted to “the new way forward” made possible by Power BI and its related technologies. Since 2013, PowerPivotPro has rapidly grown to become the leading firm in the industry, pioneering an agile, results-first methodology never before seen in the Business Intelligence space. A sought-after public speaker and author of the #1-selling Power BI book, Rob and his team would like to help you revolutionize your business and your career.

This Post Has 7 Comments
  1. 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. 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. 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. 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


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

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

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


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

Leave a Reply

Your email address will not be published. Required fields are marked *