Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

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 Poi...
Need Help
  • v-piga-msft's avatar
    v-piga-msft
    7 years ago

    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

Anonymous's avatar
Anonymous
Not applicable
7 years ago

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.

v-piga-msft's avatar
v-piga-msft
Resident Rockstar
7 years ago

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

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

    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)

  • aizamkamadin's avatar
    aizamkamadin
    Helper II
    7 years ago

    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

  • SharonHMA's avatar
    SharonHMA
    Helper I
    6 years ago

    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

     

     

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

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