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

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

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

  • 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
      Icon for Resident Rockstar rankResident 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)