cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
mjholland Regular Visitor
Regular Visitor

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

Accepted Solutions
Highlighted
mjholland Regular Visitor
Regular Visitor

Re: top and bottom rank

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.

11 REPLIES 11
mjholland Regular Visitor
Regular Visitor

top and bottom 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!

Super User
Super User

Re: top and bottom rank

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?

 

 

Super User
Super User

Re: top and bottom rank

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
Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!




Super User
Super User

Re: top and bottom rank

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.

Super User
Super User

Re: Top & Bottom 10 results, without repetitive Rank

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

Super User
Super User

Re: top and bottom rank

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

Highlighted
mjholland Regular Visitor
Regular Visitor

Re: top and bottom rank

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.

Moderator v-sihou-msft
Moderator

Re: top and bottom rank

@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

 

Super User
Super User

Re: top and bottom rank

@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 a to be a Datanaut!