Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
mjholland
Helper II
Helper II

Top & Bottom 10 results, without repetitive Rank

Hi,

 

I'm trying to work out the top and bottom ranked Retailer Regions based a % result. Each Retailer has a different number of Regions. The % result is a simple sum/count. I've found the following DAX result on a previous forum post:

 

Region Rank = rankx(ALLSELECTED('All Mults Conv Data'[Retailer Region]),[Entry %])

 

This works fine if I want to use it to filter to show the top 10 results, I just add it as a filter to my visual. But I can't use it to find the bottom 10 results. Does anyone know how I would do this? I can see the RANKX function allows for results to the ranked Ascending or Descending but this won't be suitable due to the differing number of regions per retailer.

 

Also, if I have 4 Retailer Regions with the same result, is there anyway to rank them 1,2,3,4 based on another field (say count of data points), rathan than them all having a rank of 1? You can do this in excel using the following formula, I'm just unsure how to do it in DAX:

 

=RANK(A2,$A$2:$A$10)+COUNTIF($A$2:A2,A2)-1

 

Please help!

1 ACCEPTED SOLUTION

I had to tweek it slightly, but it's working fine now. Here's what I have:

 

Top Rank = rankx(ALLSELECTED('TABLE[FIELD]),[MEASURE],,Desc)

 

Bottom Rank = rankx(ALLSELECTED('TABLE[FIELD]),[MEASURE],,ASC,Dense)-1

 

Thanks for your help.

View solution in original post

11 REPLIES 11
Baskar
Resident Rockstar
Resident Rockstar

Hi miholland,

 

Wwe can set Top 10 and Bottom 10 in single calculation measure we need to create two measures,

 

1. Rank Top = RankX(Allselected("Fieldname"),[Entry %],,1,Desc,Dense)

 

2. Rank Bottom = RankX(Allselected("Fieldname"),[Entry %],,1,Asc,Dense)

 

Try this and let me know

mjholland
Helper II
Helper II

Hi,

 

I'm trying to work out the top and bottom ranked Retailer Regions based a % result. Each Retailer has a different number of Regions. The % result is a simple sum/count. I've found the following DAX result on a previous forum post:

 

Region Rank = rankx(ALLSELECTED('All Mults Conv Data'[Retailer Region]),[Entry %])

 

This works fine if I want to use it to filter to show the top 10 results, I just add it as a filter to my visual. But I can't use it to find the bottom 10 results. Does anyone know how I would do this? I can see the RANKX function allows for results to the ranked Ascending or Descending but this won't be suitable due to the differing number of regions per retailer.

 

Also, if I have 4 Retailer Regions with the same result, is there anyway to rank them 1,2,3,4 based on another field (say count of data points), rathan than them all having a rank of 1? You can do this in excel using the following formula, I'm just unsure how to do it in DAX:

 

=RANK(A2,$A$2:$A$10)+COUNTIF($A$2:A2,A2)-1

 

Please help!

Hi miholland,

 

Wwe can set Top 10 and Bottom 10 in single calculation measure we need to create two measures,

 

1. Rank Top = RankX(Allselected("Fieldname"),[Entry %],,1,Desc,Dense)

 

2. Rank Bottom = RankX(Allselected("Fieldname"),[Entry %],,1,Asc,Dense)

 

Try this and let me know

I had to tweek it slightly, but it's working fine now. Here's what I have:

 

Top Rank = rankx(ALLSELECTED('TABLE[FIELD]),[MEASURE],,Desc)

 

Bottom Rank = rankx(ALLSELECTED('TABLE[FIELD]),[MEASURE],,ASC,Dense)-1

 

Thanks for your help.

@mjholland

 

If you have 4 Retailer Regions with the same result, you can follow below steps to rank them 1,2,3,4 based on another field (say count of data points). We can create another Top10 table to break ties based on another column.

 

  1. Create a column for Entry %. Below formula is just a sample corresponding to below test table.
    Entry%_Column =
    'All Mults Conv Data'[Sales] / SUM ( 'All Mults Conv Data'[Sales] )
    
  2. Create a column for rank of Retailer Region.
    Rank_Column = 
    RANKX ( ALL ( 'All Mults Conv Data' ), 'All Mults Conv Data'[Entry%_Column] )
    
    11.jpg
  3. Create a new Top10  table.
    Top10_Table =
    TOPN ( 10, 'All Mults Conv Data', 'All Mults Conv Data'[Rank_Column], 1 )
    
  4. Create a column to mark the Retailer Regions which have the same
    rank.
    Duplicate_Rank_Rows = 
    IF (
        CALCULATE (
            COUNTROWS ( Top10_Table ),
            ALLEXCEPT ( Top10_Table, Top10_Table[Rank_Column] )
        )
            > 1,
        TRUE(),
        FALSE()
    )
    
  5. Create a column to rank the 4 Retailer Regions based on “Count of data points” again.
    Second_Rank_Column = 
    RANKX (
        FILTER ( ALL ( Top10_Table ), Top10_Table[Duplicate_Rank_Rows] = TRUE () ),
       Top10_Table[Count of data points]
    )
    
  6. Create a column to list the final rank.
    Final_Rank =
    VAR AddedRank =
        IF (
            Top10_Table[Duplicate_Rank_Rows] = FALSE (),
            0,
            Top10_Table[Second_Rank_Column] - 1
        )
    RETURN
    Top10_Table[Rank_Column] + AddedRank
    
    22.jpg

 

@Vvelarde do you know if its possible to do both top 10 and bottom 10 in the same measure and flip between the 2 of them with a filter?





If I took the time to answer your question and I came up with a solution, please mark my post as a solution and /or give kudos freely for the effort 🙂 Thank you!

Proud to be a Super User!




Anonymous
Not applicable

Hi, rank works if i add it to a table showing only the field i am ranking by (e.g. user login) and the rank number (e.g. rank by total entries by user login). However if i add one extra field to the table like the user's department just to display it in the table, the rank field values all change to 1. Why does that happen? How can i keep rank working if i want to display more than just the field i am ranking by??

Here is an approach I used to show top 10 and bottom 5 in same visualization:

 

  • Rank Ascending (to find bottom 5): 
    Rank (Asc) of Product Line by RM = RANKX(FILTER(ALLSELECTED('Booked Order'[Product Line]),[Sum of RM]),[Sum of RM],,ASC,Dense)
  • Rank Descending (to find top 10): 
    Rank (Desc) of Product Line by RM = RANKX(FILTER(ALLSELECTED('Booked Order'[Product Line]),[Sum of RM]),[Sum of RM],,DESC,Dense)
  • Measure to identify just the top 10 and bottom 5. It returns 1 for each row that matches the criteria, else 0
    Top 10 Bottom 5 PL by RM Flag = if(CALCULATE(DISTINCTCOUNT('Booked Order'[Region]), ALLSELECTED('Booked Order'))=1,IF(OR([Rank (Desc) of Product Line by RM] <=10,[Rank (Asc) of Product Line by RM]<=5),1,0),0)

     

  • Visual-level filter on the chart where I just want the top-10 and bottom 5: filter.JPG

 

So to take it to the next level where you want to use a filter on the page to control whether you see top 10 or bottom 5, I modify the last measure above to use the Region filter on the page to determine whether I show the Top 10 or Bottom 5. (I know, not exactly real-world...) So if it is NA give me top 10, else give me bottom 5.

Top 10 Bottom 5 PL by RM Flag = IF(FIRSTNONBLANK('Booked Order'[Region],TRUE())="NA",IF([Rank (Desc) of Product Line by RM] <=10,1,0),IF([Rank (Asc) of Product Line by RM]<=5,1,0))

Hope this helps!

hi @mjholland

 

a tricky solution is create a this calculated column

 

Ranking2 = RANKX(table;field)*100000000000 + calculate(sum(dataPoint))

 

This give you diferents values to break ties.




Lima - Peru

I did just come up with a measure that always returns the n-th best whatever based on row context. So a column of numbers 1-10 would list the top 10. It definitely comes at the problem from a completely different angle but it might apply in this case. Check out this post, particluarly the example file. It's a pretty severe topic drift from the original question on the thread but the puzzle was too fun not to play with.





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Sean
Community Champion
Community Champion

RANKX doesn't have a built-in way to break ties based on another column/measure - i wish they add this though!!!

 

Look at this post (there are couple solutions listed - I personally have not tested either one)

http://community.powerbi.com/t5/Desktop/Ranking-help-please/m-p/35445#M12814

 

Including @KHorseman in case he has another idea?

 

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.