Ranking a calculated measure in a table visualisation

[Anonymous]

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 many examples on the internet that rank referring to an underlying table or calculated column, but haven't seen any that rank a calculated measure - is it even possible?

Area, CalculatedMeasure, CalculatedMeasureRank

North, 504, 2

Midlands, 421, 3

South, 751, 1

South East, 214, 4

Many thanks,

CM

[Greg_Deckler]

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]

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]

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 · Accepted solution]

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]

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