Forum Discussion
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?
Hi Anonymous,
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.
More details, you could refer to the attachment.
Best Regards,
Cherry
9 Replies
- Greg_Deckler
Community Champion
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.
v-piga-msftResident Rockstar
Hi Anonymous,
By my tests with your measure, I could get the output you desired.
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
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):
The visualisation is on the 3rd tab.
- v-piga-msft
Hi Anonymous,
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.
More details, you could refer to the attachment.
Best Regards,
Cherry
LauBulAdvocate 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/
