cancel
Showing results for
Did you mean:
Frequent Visitor

## 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

3 ACCEPTED SOLUTIONS
Super User IV

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

---------------------------------------

##### I have a NEW book! DAX Cookbook from Packt
Over 120 DAX Recipes!

Proud to be a Super User!

Resolver III

@Ababneh

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

Microsoft

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

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
8 REPLIES 8
Microsoft

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

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Resolver III

@Ababneh

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

Frequent Visitor

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

Resolver III

@Ababneh

Regards,

Chetan K

Frequent Visitor

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 🙂

Microsoft

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

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Frequent Visitor

I want to make a new topic to explain more thank you 🙂

Super User IV

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

---------------------------------------

##### I have a NEW book! DAX Cookbook from Packt
Over 120 DAX Recipes!

Proud to be a Super User!

Announcements

#### Happy New Year from Power BI

This is a must watch for a message from Power BI!