cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
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:

NameIDScoreTimes Tried
Michael3167445
James3142342
Henry1241353
Jason2131125
Jubran2411141
Henry211102
James3167443
Michael3142341

 

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

 


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

@ me in replies or I'll lose your thread!!!

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




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




View solution in original post

Resolver III
Resolver III

 

@Ababneh

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

 

 

View solution in original post

Microsoft
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. 
Rank one by one .jpg

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

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.

View solution in original post

8 REPLIES 8
Microsoft
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. 
Rank one by one .jpg

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

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.

View solution in original post

Resolver III
Resolver III

 

@Ababneh

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

 

 

View solution in original post

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

@Ababneh

 

follow the below

 

 

Snap.PNG

 

Regards,

Chetan K

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

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.

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

Super User IV
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

 


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

@ me in replies or I'll lose your thread!!!

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




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




View solution in original post

Helpful resources

Announcements
secondImage

Experience what’s next for Power BI

Join us for an in-depth look at the new Power BI features and capabilities at the free Microsoft Business Applications Launch Event.

secondImage

Power BI Women

Join our monthly meetings and learning sessions.

secondImage

Congratulations!

We are excited to announce the Power BI Super Users!

secondImage

The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors