RANKX-apalooza: Within and Across Groups, With Filters, Etc.

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

  SUM(Sales[Units])

(Which is just a simple base measure)

  [Overall Store Rank] :=

  IF(HASONEVALUE(Stores[StoreID]),
     RANKX(ALL(Stores), [Units Sold]),
     BLANK()
    )

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

  IF(HASONEVALUE(Stores[StoreID]),
     RANKX(ALL(Stores[Store Name]), [Units Sold]),
     BLANK()
    )

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

  IF(HASONEVALUE(Stores[StoreID]),
     RANKX(ALLSELECTED(Stores), [Units Sold]),
     BLANK()
    )

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

  IF(HASONEVALUE(Stores[Region]),
     RANKX(ALL(Stores[Region]),
           CALCULATE([Units Sold], ALL(Stores[Area]))
          ),
     BLANK()
    )

  [Region Rank Within Area] :=

  IF(HASONEVALUE(Stores[Region]),
     RANKX(ALL(Stores[Region]), [Units Sold]),
     BLANK()
    )

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

  IF(HASONEVALUE(Stores[Region]),
     RANKX(ALLSELECTED(Stores[Region]),
           CALCULATE([Units Sold], 
                     ALLEXCEPT(Stores, Stores[Region])
                    )
           ),
     BLANK()
    )

Fun, right?