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
Ababneh
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
Greg_Deckler
Super User
Super User

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!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

View solution in original post

chethan
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

v-jiascu-msft
Employee
Employee

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
v-jiascu-msft
Employee
Employee

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

 

 

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 🙂

Greg_Deckler
Super User
Super User

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!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

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.