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
kmes912
Helper I
Helper I

RankX two different sets of ranks

I need to be able to rank (and eventually percentile) two comparisons, but display in the same table. Right now the "groupings" and "Name" come from the same lookup attribute table. The score is in the data table. I want to retain the overall ranking but also be able to display the ranking out of the total in that group. 

 

This is what I have currently in the "Rank All", which works but cannot figure out the right combination to get to group
 
Rank = RANKX(ALL('Attributes'),[Score],,DESC,Dense)
 
If I change the Rank to be RANKX(ALL('Attributes'[Names]),[Score],,DESC,Dense), this works if i filter the "groupings" but want to show it live without the need to filter so we can extract the table in one big export. 

 

GroupingsNamesScoreRank AllRank by Group
Group 2Unit 18611
Group 2Unit 28222
Group 3Unit 38131
Group 4Unit 48041
Group 1Unit 57951
Group 1Unit 67961
Group 2Unit 77873
Group 2Unit 87784
Group 1Unit 97593
Group 2Unit 1035105
1 ACCEPTED SOLUTION
amitchandak
Super User
Super User

@kmes912 ,

if you do want filter to change rank

Rank All = RANKX(ALL('Attributes'Groupings ,'Attributes' [Names]),[Score],,DESC,Dense)

 

Rank = RANKX(ALL('Attributes'[Names]),[Score],,DESC,Dense)

 

Work with filter

 

Rank All = RANKX(ALLSELECTED('Attributes'Groupings ,'Attributes' [Names]),[Score],,DESC,Dense)

 

Rank = RANKX(ALLSELECTED('Attributes'[Names]),[Score],,DESC,Dense)

 

Measure Rank: https://www.youtube.com/watch?v=DZb_6j6WuZ0&list=PLPaNVDMhUXGaaqV92SBD5X2hk3TMNlHhb&index=40
https://www.youtube.com/watch?v=cN8AO3_vmlY&t=25627s

 

 

refer if needed

Power BI Rank Across dimension tables: https://youtu.be/X59qp5gfQoA

View solution in original post

2 REPLIES 2
Jihwan_Kim
Super User
Super User

Hi,

Please check the below picture and the attached pbix file.

 

Jihwan_Kim_0-1679975700573.png

 

Jihwan_Kim_1-1679976332889.png

 

 

If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.


Go to My LinkedIn Page


amitchandak
Super User
Super User

@kmes912 ,

if you do want filter to change rank

Rank All = RANKX(ALL('Attributes'Groupings ,'Attributes' [Names]),[Score],,DESC,Dense)

 

Rank = RANKX(ALL('Attributes'[Names]),[Score],,DESC,Dense)

 

Work with filter

 

Rank All = RANKX(ALLSELECTED('Attributes'Groupings ,'Attributes' [Names]),[Score],,DESC,Dense)

 

Rank = RANKX(ALLSELECTED('Attributes'[Names]),[Score],,DESC,Dense)

 

Measure Rank: https://www.youtube.com/watch?v=DZb_6j6WuZ0&list=PLPaNVDMhUXGaaqV92SBD5X2hk3TMNlHhb&index=40
https://www.youtube.com/watch?v=cN8AO3_vmlY&t=25627s

 

 

refer if needed

Power BI Rank Across dimension tables: https://youtu.be/X59qp5gfQoA

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.

Top Solution Authors