Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
8 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 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?

Need Help
  • v-piga-msft's avatar
    v-piga-msft
    8 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