Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.

Reply
CloudMonkey
Post Prodigy
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

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])


Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Power BI Cookbook Third Edition (Color)

DAX is easy, CALCULATE makes DAX hard...

View solution in original post

5 REPLIES 5
Greg_Deckler
Super User
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.

 

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/

 



Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Power BI Cookbook Third Edition (Color)

DAX is easy, CALCULATE makes DAX hard...

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

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.

 

 



Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Power BI Cookbook Third Edition (Color)

DAX is easy, CALCULATE makes DAX hard...

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])


Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Power BI Cookbook Third Edition (Color)

DAX is easy, CALCULATE makes DAX hard...

Hi @CloudMonkey

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

Helpful resources

Announcements
Feb2025 Sticker Challenge

Join our Community Sticker Challenge 2025

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

Jan NL Carousel

Fabric Community Update - January 2025

Find out what's new and trending in the Fabric community.