A Power BI Technique Mined from the Power Pivot Archives
Below, you will find one of our all-time favorite Power BI techniques. In the ten (yes ten) years we’ve been operating this site, we’ve written over 1,000 articles on Power BI. Hard to imagine - even for us - but true.
Years ago, we first wrote up this technique in the context of Power Pivot – Power BI’s cousin and predecessor, which we like to call “Power BI in Excel.”
Since the two products share the same brains (DAX and M), this technique is more relevant today than ever. Everything below is 100% legit for Power BI – the same data model “shape,” the same DAX, etc. – but now you can use it to power up your Power BI visuals, and not just PivotTables. Enjoy!
Post by Rob Collie
One from the Reference Stacks
I sometimes find RANKX() a bit perplexing. So awhile back I made myself a workbook of examples, which I will share with you today:
List of Stores, Grouped by Region, and then Ranked by Units Sold –
Both Overall (vs. All Stores) and Within Region (Just vs Stores Within Same Region)
Background – The Tables
Simple 3-table model:
And for our RANKX Purposes, We Only Care About Stores and Sales
(Calendar Table is Not Referenced in Any Formulas for this Post)
Stores Table – One Row Per Store, With ID, Region, Name, and Area Columns
First Batch of Measure/Calculated Field Formulas
Same Example from Above
[Units Sold] :=
(Which is just a simple base measure)
[Overall Store Rank] :=
RANKX(ALL(Stores), [Units Sold]),
The IF(HASONEVALUE(),…,BLANK()) test prevents us from ranking subtotals (like region) as if they were stores.
And we RANKX() against ALL(Stores) because that’s what we want here – each store’s rank versus all other stores.
[Store Rank Within Region] :=
RANKX(ALL(Stores[Store Name]), [Units Sold]),
Only thing different here is that we rank against ALL(Stores[Store Name]) rather than ALL(Stores). Which means we clear the filter on Store Name, but *keep* the filter on Region, which means we end up just looking at all of the stores in the current region.
Now, Filter on Region
Our previous pivot displayed all regions – North, South, East, West and Central. Now we set a Row Labels filter so that the pivot just displays Central and East:
Now We Filter Regions So That We Only See Central and East
Which Gives Us:
Only 16 Stores are Now Displayed, but Overall Rank is Still Ranking Against all 40+ Stores
(Which is Sometimes What We Want, Sometimes Not)
But what if we want to just rank against all *visible* stores, like this:
This New Measure/Calc Field Ranks from 1 to 16
(Just the Visible Stores, But Still Independent of Region)
And that formula is…
[Store Rank Within All Visible Stores] :=
RANKX(ALLSELECTED(Stores), [Units Sold]),
OK cool. Moving on…
Ranking by Non-Unique Columns
So far, we’ve just been ranking single stores, which are each represented by a single row in the Stores table:
StoreID and Store Name are Unique, but Region and Area Obviously Have Duplicate Values
(Simply because each Area and Region has more than one store in it)
So what if we want to do something like this, where we rank Regions rather than individual stores?
Now Ranking Regions Rather than Stores
The formulas are:
[Overall Region Rank] :=
CALCULATE([Units Sold], ALL(Stores[Area]))
[Region Rank Within Area] :=
RANKX(ALL(Stores[Region]), [Units Sold]),
In both cases, we rank across ALL(Stores[Region]) because that does produce a list of just the regions (with all regions filters cleared).
But in the second case, we just rank using [Units Sold], whereas in the first case, we need to clear the Area filter before fetching the values for [Units Sold] – so that we “count” regions in other areas “against” regions in the current area. Yeah, a bit weird. This is why I have a reference workbook – cool results, but difficult to re-engineer every time I need them.
It Goes On From Here…
…but the post is running long and getting more intensive than I think is healthy.
So let’s just share one more quick formula, for those who are intrepid:
[Region Rank Within All Visible Regions] :=