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.
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
Solved! Go to Solution.
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
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
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
It is not working! 😕
Is there a way to save the name of Rank 1? because I want to make a measure to say, "The Winner Is "&Winnername?
Thanks 🙂
Hi @Ababneh,
Which solution did you use in your scenario? In my example, the rank 1 is static relatively. So you can pick it up with a filter [order] = 1.
Best Regards!
Dale
I want to make a new topic to explain more thank you 🙂
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
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 |
---|---|
111 | |
100 | |
80 | |
64 | |
58 |
User | Count |
---|---|
146 | |
110 | |
93 | |
84 | |
67 |