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:
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.”
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 P3 Adaptive 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.
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:
Neat Huh? We’ll “Drill Down” Into This Technique Next Time
