cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

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!:
The Definitive Guide to Power Query (M)

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!:
The Definitive Guide to Power Query (M)

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!:
The Definitive Guide to Power Query (M)

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!:
The Definitive Guide to Power Query (M)

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

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.