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
PetyrBaelish
Resolver III
Resolver III

Ranking a measure

I know this question has been aasked before, I've tried to follow some of the answers but failed...

 

I have a table called Results. This has ...

 

- a column called Player

- a column called Points

- a calculated column called Year-Month

 

I have then created a Measure called SumPoints: SumPoints = SUMX(Results,[Points])

 

I then created a visual with these 2 columns & measure - no summarization on the player or year-month columns. The output looked similar to:

 

Player ---- Year-Month ---- SumPoints

Daniel ---- 2016-Jun ------ 40

Jason ----- 2016-Jul ------ 38

Daniel ---- 2016-Dec ------ 36

Lee ------- 2016-Feb ------ 35

Stephen -- 2016-June ------ 33

 

I then want to rank these, and have created the following measure:

 

SumPointsRank = RANKX(ALLSELECTED(Results),[SumPoints],[SumPoints],DESC)

 

This however hasn't worked (everything is equal 1)

 

What I'm expecting is row 1 (Daniel) to be ranked 1, row 2 (Jason) to be ranked 2 and so on.

 

Can somebody help please?

1 ACCEPTED SOLUTION

Hi @PetyrBaelish,

 

Based your sample, you could have a try with the measure below.

 

SumPointsRank = 
Var summry=SUMMARIZE(ALLSELECTED(Results),[Player],"Sum",SUM(Results[Points]))
var tmp=ADDCOLUMNS(summry,"RNK",RANKX(summry,[Sum],,DESC,Dense))
return
MAXX(FILTER(tmp,[Player]=SELECTEDVALUE(Results[Player])),[RNK])

If you don't want to create a temp table, you also could use the measure2.

 

Measure2=
RANKX(ALLSELECTED(Results),CALCULATE(SUM(Results[Points]),ALLEXCEPT(Results,Results[Player])),,DESC,Dense)

Here is the output result.

 

Capture.PNG

 

 

More details, you could refer to the attachment.

 

Best  Regards,

Cherry

Community Support Team _ Cherry Gao
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

9 REPLIES 9
LauBul
Advocate I
Advocate I

Perhaps I just needed an explained breakdown, because I couldn't get the above solutions to work for me. In case others are still having issues, the below link helped a lot:
 
https://powerbi.tips/2017/07/ranking-values-with-measures/

v-piga-msft
Resident Rockstar
Resident Rockstar

Hi @PetyrBaelish,

 

By my tests with your measure, I could get the output you desired.

 

test result.PNG

 

If you still need help, please share a dummy pbix file which can reproduce the issue and your desired output, so that we can help further investigate on it? You can upload it to OneDrive or Dropbox and post the link here. Do mask sensitive data before uploading.)

 

You also could have a reference of my attachment.

 

Best  Regards,

Cherry

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

Thanks for your response - I've had a look at your file and can't see any difference between yours and mine, yet my ranking isn't working. I also tried Greg's suggestion buy it didn't yield the required results.

 

Here's my file (containing dumy data):

 

Sample File

The visualisation is on the 3rd tab.

Hi @PetyrBaelish,

 

Based your sample, you could have a try with the measure below.

 

SumPointsRank = 
Var summry=SUMMARIZE(ALLSELECTED(Results),[Player],"Sum",SUM(Results[Points]))
var tmp=ADDCOLUMNS(summry,"RNK",RANKX(summry,[Sum],,DESC,Dense))
return
MAXX(FILTER(tmp,[Player]=SELECTEDVALUE(Results[Player])),[RNK])

If you don't want to create a temp table, you also could use the measure2.

 

Measure2=
RANKX(ALLSELECTED(Results),CALCULATE(SUM(Results[Points]),ALLEXCEPT(Results,Results[Player])),,DESC,Dense)

Here is the output result.

 

Capture.PNG

 

 

More details, you could refer to the attachment.

 

Best  Regards,

Cherry

Community Support Team _ Cherry Gao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Anonymous
Not applicable

Thanks for this solution. I had a similar requirement and it worked like a charm.

However, I have a category of Players as well, say A, B and C and I want to rank the Players within these categories. Wondering what change should I do in the below temp table measure to get the desired result.

Thanks in advance!!

SumPointsRank = 
Var summry=SUMMARIZE(ALLSELECTED(Results),[Player],"Sum",SUM(Results[Points]))
var tmp=ADDCOLUMNS(summry,"RNK",RANKX(summry,[Sum],,DESC,Dense))
return
MAXX(FILTER(tmp,[Player]=SELECTEDVALUE(Results[Player])),[RNK])

 

Hi @v-piga-msft 

 

If I want to rank data for individuals each and every month in a visual how would I adjust your formula.  I tried:

 

Rank TLeads Owner = RANKX(ALLSELECTED(Merged),CALCULATE(SUM(Merged[Count Leads]),ALLEXCEPT(Merged,Merged[Lead Owner],'Calendar'[Month Year])),,DESC,Dense)

However I'm getting totally wrong results.

 

Also, I have the visual filtered for only certain team members so I want the rankings for them alone

 

Effectively I want to sore each month in a clustered column chart from highest to lowest based on the legend.

 

Thanks, Sharon

 

 

why when im using measure2, its give a different result(wrong result) compare to using temp table.

but if im using temp table.. i cant see the rank over the time.. because it give the same result.

pls help

Thansk Cherry - I made a minor tweak to include the Year-Month in the ALLEXCEPT function (as once the real data was included the output wasn't correct) - and it has worked. Thank you.

 

Final measure:

 

Measure2 = RANKX(ALLSELECTED(Results),CALCULATE(SUM(Results[Points]),ALLEXCEPT(Results,Results[Player],Results[Year-Month])),,DESC,Dense)

Greg_Deckler
Super User
Super User

I believe you are looking for something along the lines of:

 

Rank Measure = 
VAR __player = MAX([Player])
VAR __table = SUMMARIZE(ALLSELECTED('Table1'),[Player],"__Points",[SumPoints])
VAR __table1 = ADDCOLUMNS(__table,"__Rank",RANKX(__table,[SumPoints]))
RETURN
MAXX(FILTER(__table1,[Player]=__player),[__Rank])

There are other ways to do it.


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