Ranks and Exceptions that “Bubble Up” to Subtotals

 
image

One Training Session, Two (Three?) Blog Posts Inspired

Every now and then, I find myself training someone who I just KNOW is going to be teaching ME things in the near future.  The questions they ask are the dead giveaways.  And the next time we meet, they will be Darth Vader, telling my Ben Kenobi about how the circuit is complete.

I had the pleasure of teaching someone like this quite recently.  He forced me to write some pretty nifty calcs, or at least some that *I* think are nifty.

The Formula


Let’s cut to the chase.  Here’s the measure formula:

[Product Sales Rank]=

IF(HASONEVALUE(Products[ProductName]),
   RANKX(ALL(Products), [Total Sales]),
   MINX(VALUES(Products[ProductName]),
        RANKX(ALL(Products), [Total Sales])
       )
  )

Explained:  First Branch of the IF

The IF checks to see if the current measure cell is in the context of a single product, and if so, just performs a rank of that product against all other products:

image

IF(HASONEVALUE(Products[ProductKey]),
   RANKX(ALL(Products), [Total Sales]),
   MINX(VALUES(Products[ProductKey]),
        RANKX(ALL(Products), [Total Sales])
       )
  )

So really, that branch is just a normal RANKX measure.

The Second Branch is the Nifty Branch

If the current measure cell is NOT a single product, that’s when the second branch “fires.”

image

IF(HASONEVALUE(Products[ProductKey]),
   RANKX(ALL(Products), [Total Sales]),
  
MINX(VALUES(Products[ProductKey]),
        RANKX(ALL(Products), [Total Sales])
       )
  )

The blue part of the formula, loosely translated, does this:

For each product that is “valid” in the current measure cell, go find its rank amongst all products.  Then return the lowest such rank from all of those products.

So It’s Not Really “Bubbling Up.”  It’s Working Overtime.

One of the rules I always teach people in training courses or in PowerPivotPro School is that no measure cell EVER impacts another measure cell.  Each measure cell is calculated independently, as if it were an island.

In reality, the “blue” cells of the pivot (the ones where there is more than one product “active” in that context”) are forcing the Power Pivot calc engine to work MUCH harder than it does for the green (single product) cells.

image

So, in essence, every product in the Products table gets ranked 3 times – once at each level of the pivot.  Don’t be shocked if this sometimes results in slow pivots.

(In fact, whenever you see one “X” function like RANKX or MINX nested inside another “X” function, that’s generally a clue that you may see slow slicer clicks, since each X function is itself a “go do something a bunch of times” machine.)

Not Just for Ranks!

Now imagine you write another measure.  One that detects whether something is going wrong down in the details and then “bubbles that up” to a higher level:

Bubble up Exceptions in Power Pivot?  Yes we can!

Neat Huh?  We’ll “Drill Down” Into This Technique Next Time

5 Responses to Ranks and Exceptions that “Bubble Up” to Subtotals

  1. frederic gilbert says:

    Hello Rob,

    Very interesting formula. I had tried it on a data set of web stats spanning one full year for 750+ websites.The formula works but its results are unexpected…Def, I have to put more work into it.

    In any case, looking forward to the next Drill-Down post.

    Thx for sharing your powerpivot know-how

    • powerpivotpro says:

      Frederic that sounds like a VERY interesting data set/problem!

      I’ll take a shot in the dark and guess that you are getting ties that are causing the RANKX function to return unexpected results. The ties always get me.

  2. A. Skywalker says:

    Luke. I will be your father. Some day.

    I’m glad I could help inspire content. Thanks for the kinds words, and thanks for always striving to teach the world something new about PowerPivot.

    • powerpivotpro says:

      As Emperor Palpatine once said: “Goooooood… GOOOOOOOOOOD!”

      You rock, sir. One small nitpick though – you are Darth Vader in this particular “moviephor” (movie metaphor – yeah I just coined that, things move kinda fast around here). I’m Obi-Wan (the grizzled Alec Guinness version, not the hearth-throb Ewan MacGregor version). And you’re about to chop me down with a lightsaber, only I’m martyring myself so I can come back and haunt everyone for the next two movies as the Ghost of Christmas EARLIER().

      • powerpivotpro says:

        Holy cow am I sleepy. You signed you comment *A* Skywalker, not *L* Skywalker. You were properly in character the whole time, my apologies. I’m still going to haunt everyone as the Ghost of Christmas EARLIER(), though.

Leave a Reply