Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
8 years ago
Solved

Ranking a calculated measure in a table visualisation

Hello,   I have the following table as a visualisation but without rank column on the right hand side. Please can you tell me how to add this if the other column is a calculated measure? I've seen ...
Need Help
  • Greg_Deckler's avatar
    Greg_Deckler
    8 years ago

    Note that I improved this with a non-trivial case of measure ranking, PBIX attached.

     

    Measure Rank 1 = 
VAR __current = CALCULATE(MAX([Column1]))
VAR __table = SUMMARIZE(ALL(Table1),[Column1],"__Measure",[Measure])
VAR __table1 = ADDCOLUMNS(__table,"__Rank",RANKX(__table,[__Measure]))
RETURN MAXX(FILTER(__table1,[Column1]=__current),[__Rank])
Anonymous's avatar
Anonymous
Not applicable
8 years ago

Thanks for trying but I'm afraid it's too complicated. I'll have to export the table into excel and just use the rank function.

 

Thanks,

 

CM

Greg_Deckler's avatar
Greg_Deckler
Icon for Community Champion rankCommunity Champion
8 years ago

I'm not sure how a single line is overly complicated:

 

Measure Rank = RANKX(ALL(Table1),[Measure])

Or 2 lines for your Standard Deviation question.

 

Measure StdDev = 
VAR __table = SUMMARIZE(Table1,[Column1],"__Measure",[Measure])
RETURN STDEVX.P(__table,[__Measure])

But, perhaps it is. People have different definitions of complex.

 

 

  • Greg_Deckler's avatar
    Greg_Deckler
    Icon for Community Champion rankCommunity Champion
    8 years ago

    Note that I improved this with a non-trivial case of measure ranking, PBIX attached.

     

    Measure Rank 1 = 
VAR __current = CALCULATE(MAX([Column1]))
VAR __table = SUMMARIZE(ALL(Table1),[Column1],"__Measure",[Measure])
VAR __table1 = ADDCOLUMNS(__table,"__Rank",RANKX(__table,[__Measure]))
RETURN MAXX(FILTER(__table1,[Column1]=__current),[__Rank])
    • v-juanli-msft's avatar
      v-juanli-msft
      Icon for Community Support rankCommunity Support
      8 years ago

      Hi Anonymous

      As tested, Greg_Deckler's solution works, does it exactly meet your goal?

      If so, could you kindly mark it as a solution?

       

      Best Regards

      Maggie