cancel
Showing results for 
Search instead for 
Did you mean: 
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!

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

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

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





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

Proud to be a Super User!




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!

Vvelarde
Community Champion
Community Champion

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
KHorseman
Community Champion
Community Champion

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
PBI_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

Get Ready for Power BI Dev Camp

Microsoft named a Leader in The Forrester Wave

Microsoft received the highest score of any vendor in both the strategy and current offering categories.

Get Ready for Power BI Dev Camp

Power BI Dev Camp - September 30th, 2021

Mark your calendars and join us for our next Power BI Dev Camp!

PowerPlatform 768x460.png

Microsoft Learn

Check out our new Discover Your Career Path blog post series and get all the details.

Top Solution Authors