Forum Discussion
Rank one by one
Hey all, I'm a new user to Power BI, but I learn fast, I only need guidance here what to look for.
I have a table like this:
| Name | ID | Score | Times Tried |
| Michael | 3167 | 44 | 5 |
| James | 3142 | 34 | 2 |
| Henry | 1241 | 35 | 3 |
| Jason | 2131 | 12 | 5 |
| Jubran | 2411 | 14 | 1 |
| Henry | 2111 | 0 | 2 |
| James | 3167 | 44 | 3 |
| Michael | 3142 | 34 | 1 |
What I want to do in Power BI is:
1) Show the top 5 scores
2) And the same scores (like James and Michael), I want James to be ranked first because he tried less than Michael
3) I want the results to be shown in 5 rectangles that are above each other, the top one is the top 1, and the last one is top 5, and the higher the score the bigger the rectangle
4) I want only 5 results to be shown not the whole scores.
I would really appreciate your help and I know it takes time and I need to practice more, but I only want to do this very fast because I have to finish it this weekend. Thanks again
Well, this will almost certainly involve a RANKX measure:
https://msdn.microsoft.com/en-us/library/gg492185.aspx
You may have to do something like use TOPN to seed your RANKX and sort in decreasing order perhaps so that 1 is 5 and 5 is 1 so that you could use your rank in the manner you described in a stacked column chart:
https://msdn.microsoft.com/en-us/library/gg492198.aspx
Follow the below steps
Create 2 Mesures
Scores = CALCULATE(SUM(Table1[Score]))
Then Rank Measures
Ranking = RANKX ( ALL ( Table1[Name]), [Scores],,DESC,Dense)
to Filter top 5 use this measure
Top 5 Name = IF( HASONEVALUE( 'Table1'[Name]) , IF ( [Ranking] <= 5 , [Ranking] , BLANK() ) )
& filter it as "is not Blank" from visual level filters
Regards,
Chetan K
Hi Ababneh,
I think the column ID is unique though there are duplicates 3142. And the ID should be unique. If so, it's easy to find the top 5.
1. In Query Editor, sort "Score" by Desc and sort "Times Tried" by Asc;
2. Add an index from 1 and change the column into "Order". Then the rank is ready;
3. Set ID sort by "Order";
4. Create a visual "Funnel" and set field as the picture showed.
Best Regards!
Dale
8 Replies
Well, this will almost certainly involve a RANKX measure:
https://msdn.microsoft.com/en-us/library/gg492185.aspx
You may have to do something like use TOPN to seed your RANKX and sort in decreasing order perhaps so that 1 is 5 and 5 is 1 so that you could use your rank in the manner you described in a stacked column chart:
https://msdn.microsoft.com/en-us/library/gg492198.aspx
Follow the below steps
Create 2 Mesures
Scores = CALCULATE(SUM(Table1[Score]))
Then Rank Measures
Ranking = RANKX ( ALL ( Table1[Name]), [Scores],,DESC,Dense)
to Filter top 5 use this measure
Top 5 Name = IF( HASONEVALUE( 'Table1'[Name]) , IF ( [Ranking] <= 5 , [Ranking] , BLANK() ) )
& filter it as "is not Blank" from visual level filters
Regards,
Chetan K
Thank you :) All your answers helped me, but this one specially did :) Thank you.
I have two more questions, I did ranking for name and for departments, how can I put the name of the top 1 rank in a Card, and his score in another rank, and the name of of the top department name (also ranked first).
Thanks
Hi Ababneh,
I think the column ID is unique though there are duplicates 3142. And the ID should be unique. If so, it's easy to find the top 5.
1. In Query Editor, sort "Score" by Desc and sort "Times Tried" by Asc;
2. Add an index from 1 and change the column into "Order". Then the rank is ready;
3. Set ID sort by "Order";
4. Create a visual "Funnel" and set field as the picture showed.
Best Regards!
Dale
