skip to Main Content

Post by Rob Collie

The Ranking of a Single Thing Can Vary Depending on What You Rank it Against.  RANKX() Can Handle All of That.

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:

The Ranking of a Single Thing Can Vary Depending on What You Rank it Against.  RANKX() Can Handle All of That.

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:

image

And for our RANKX Purposes, We Only Care About Stores and Sales
(Calendar Table is Not Referenced in Any Formulas for this Post)

image

Stores Table – One Row Per Store, With ID, Region, Name, and Area Columns

First Batch of Measure/Calculated Field Formulas

The Ranking of a Single Thing Can Vary Depending on What You Rank it Against.  RANKX() Can Handle All of That.

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:

image

Now We Filter Regions So That We Only See Central and East

Which Gives Us:

image

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:

RANKX() Across Categories/Groups, But Respecting Filters This Time!

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:

image

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?

RANKX() Over Non-Unique Columns

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? Smile

Rob Collie

One of the founding engineers behind Power Pivot during his 14-year career at Microsoft, and creator of the world’s first cloud Power Pivot service, Rob is one of the foremost authorities on self-service business intelligence and next-generation spreadsheet technology.

This Post Has 21 Comments
  1. RANKX is so useful in my reports! Combine with SWITCH TRUE, it is magic 🙂
    http://powerpivotpro.com/2015/03/the-diabolical-genius-of-switch-true/

    I do have a question though…

    Let’s say that in this example, I rank against Unit Sold THIS YEAR and with the help of SWITCH TRUE, I only show the top 10 stores of all regions.

    Something like that :

    Strore Name Unit Sold
    Store HH 500
    Store X 496
    … top 10 only

    Any idea how I can also display the Unit Sold LAST YEAR of these top 10 stores???

    This way, you can see if the presence of a particular store is caused by huge progression vs last year or if it seems normal.

  2. Hello Rob,

    Thanks for your time to show applications for the RANKX function; I have pegged this page into my memory.

    Also helpful was the section where you mentioned you keep workbook(s) of examples for functions that can, at times, be perplexing. There are definitely at least some DAX functions where I have had a flash of insight to implement a solution, but not kept a record in an examples workbook, only to find myself later pouring over multiple formulas in various workbooks to find that “right” one.

  3. I don’t know if it’s the right place to ask, but I have a nut too hard for me to crack, around those RANKX/FILTER and that would be great if you could help me just a little.

    I have a fact table with Country / Brand / Product / Amount

    What I’d like to write is a “Top 3 Brand” measure that will give for each Country the SUM(Amount) of the Top 3 Brands, based on their respective SUM(Amount)

    Exemple here :
    https://drive.google.com/file/d/0BzC3Mc-BjWN6ZGVuMWc5SEhEem8/view?usp=sharing

    In yellow the facts, in orange the intermediary measures, in green what I’m trying to acheive.

    I’m OK for the “Brand Ranking” measure, with someting like ”
    Rank Net Market:=RANKX(ALL(Tableau1[Market]); CALCULATE(SUM(Tableau1[Net Budget in MUSD])))

    I could have the correct “Top 3 Brand” value in a result spreadsheet like :
    Country / Brand / Brand Ranking / Sum(Amount)
    and sorting the rank and filtering the top 3 rank, but that would force me to keep the “Brand” column.

    Thanks in advance.

  4. Please consider including a non-normalized example. I have been hunting for weeks for a RANKX example where there is only 1 non normalized Table. Please show me how to RANKX on the ‘SALES'[Store ID] field without the Stores table? As though there is no Stores table and therefore the summarizing column has duplicate values.
    Where you have “RANKX(ALL(Stores[Store Name]), [Units Sold])” in your first ranking, what would you replace the “ALL(Stores[Store Name])” with if there was no related table with just the stores?

    I understand this might be a forum Q, but as I haven’t been able to find a post anywhere I am trying to suggest it hereas an extension of this post. Perhaps this context related Q has a different place in the blog postings that I have missed.

    Thanks,
    J

  5. Being a newbie to DAX it would be great to have the source tables for following along. It’s the best way for learning, agreed?

  6. Excellent post, how could know how many stores there are in “top 10” with the measure Overall Store Rank for each Region?

    For example, Central has 2 , Store x and Store ZZ, East has Store HH, QQ2 ….

    Thank you very much.

  7. Hi Rob, thanks for your post. I’m trying to follow the steps outlined here, but no matter what, it always ranks everything as 1. I’m using a single flat table and trying to rank cities by sales. The formulas are sum of sales = SUM(Orders[Sales]) as a measure and RANK = RANKX(ALL(‘Orders'[City]),[sum of sales]). Any ideas why this wouldn’t work? Thank you!

  8. Nearly the Same as Bethany Above using DAX in Power BI. any help appreciated. Keep Getting “2” using this formula: rankx test 4 = if(HASONEVALUE(ON_AIR_COMPARE_W_PLEDGE_IDS[Program]),rankx(ON_AIR_COMPARE_W_PLEDGE_IDS,”program”,”percentage of pledges”,DESC,Dense), blank())

  9. I used RANKX on a table that has a many-to-one relationship with a Date table, yet when I set a page filter for current year, current month in a pivot table page filter, I get ranks for all rows in the table, as if there is no context filtering from the Date table. For example, if I have a Date table with child Order table and grandchild OrderDetail table (for order line items), and I constrain Date table to May 2016 dates, RANKX(Order,[SumOfPrice]) ranks all rows in the Order table, as if there is no filter context coming from the Date table (as if expression were RANKX(ALL(Order),[SumOfPrice])). Why do I get this behavior. I have created the relationships between the tables in Power Pivot, so why is there no context filtering on the table argument of RANKX when I select only certain dates from the Date table in the pivot table?

    1. i believe that you should use RANKX(ALL(order)[name of column which items you are ranking] , [sumOfPrice] )) . By using ALL(Order) every other filter is being ignored

  10. Hi, I can get this to work when ranking based on high number, example: when highest sales = rank 1. When flipping this to rank based on lowest number = rank 1, the rank by region is calculating incorrectly. Can anyone please help?

  11. Hi Rob,

    Thank you for another great article.

    I have spent hours on end researching and testing without any joy so far, so I hope you or anyone else can help 🙂

    I am trying to create a ‘Store Rank Within Area’. I need the rank of the store within the areas, and I need this to remain the same when filtering specific regions. In the example above, could you please share how to do this?

    It would be greatly appreciated.

    Thanks,

    Ste

  12. Thank you Rob for this great post !
    How do you calculate this measure : [Overall Area Rank] ?
    I can’t do it.
    Thanks in advance

Leave a Comment or Question