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])
Greg_Deckler's avatar
Greg_Deckler
Icon for Community Champion rankCommunity Champion
8 years ago

Yes, it is possible. Can you post sample source data, measure formula, etc.? Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

 

The basic process is generally to create a temp table using a VAR that emulates the table you are going to display. You can then use ADDCOLUMNS to create your rank column.

 

This article may also help although it is not specific to your issue:

 

https://www.sqlbi.com/articles/use-of-rankx-in-power-bi-measures/

 

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