cancel
Showing results for
Did you mean:  Post Prodigy

## 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 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

1 ACCEPTED SOLUTION  Super User

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])
RETURN MAXX(FILTER(__table1,[Column1]=__current),[__Rank])```

Become an expert!: Enterprise DNA
External Tools: MSHGQM
Latest book!:
Mastering Power BI 2nd Edition  DAX is easy, CALCULATE makes DAX hard...
5 REPLIES 5  Super User

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.

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

Become an expert!: Enterprise DNA
External Tools: MSHGQM
Latest book!:
Mastering Power BI 2nd Edition  DAX is easy, CALCULATE makes DAX hard...  Post Prodigy

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  Super User

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.

Become an expert!: Enterprise DNA
External Tools: MSHGQM
Latest book!:
Mastering Power BI 2nd Edition  DAX is easy, CALCULATE makes DAX hard...  Super User

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])
RETURN MAXX(FILTER(__table1,[Column1]=__current),[__Rank])```

Become an expert!: Enterprise DNA
External Tools: MSHGQM
Latest book!:
Mastering Power BI 2nd Edition  DAX is easy, CALCULATE makes DAX hard...  Community Support

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  