Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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!
Solved! Go to 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.
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
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.
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.
Entry%_Column = 'All Mults Conv Data'[Sales] / SUM ( 'All Mults Conv Data'[Sales] )
Rank_Column = RANKX ( ALL ( 'All Mults Conv Data' ), 'All Mults Conv Data'[Entry%_Column] )
Top10_Table = TOPN ( 10, 'All Mults Conv Data', 'All Mults Conv Data'[Rank_Column], 1 )
Duplicate_Rank_Rows = IF ( CALCULATE ( COUNTROWS ( Top10_Table ), ALLEXCEPT ( Top10_Table, Top10_Table[Rank_Column] ) ) > 1, TRUE(), FALSE() )
Second_Rank_Column = RANKX ( FILTER ( ALL ( Top10_Table ), Top10_Table[Duplicate_Rank_Rows] = TRUE () ), Top10_Table[Count of data points] )
Final_Rank = VAR AddedRank = IF ( Top10_Table[Duplicate_Rank_Rows] = FALSE (), 0, Top10_Table[Second_Rank_Column] - 1 ) RETURN Top10_Table[Rank_Column] + AddedRank
@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?
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 (Asc) of Product Line by RM = RANKX(FILTER(ALLSELECTED('Booked Order'[Product Line]),[Sum of RM]),[Sum of RM],,ASC,Dense)
Rank (Desc) of Product Line by RM = RANKX(FILTER(ALLSELECTED('Booked Order'[Product Line]),[Sum of RM]),[Sum of RM],,DESC,Dense)
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)
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.
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.
Proud to be a Super User!
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?
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
113 | |
99 | |
80 | |
70 | |
59 |
User | Count |
---|---|
149 | |
114 | |
107 | |
89 | |
67 |