topic Re: Measure Ranking and Aggregations in Quick Measures Gallery

Measure Ranking and Aggregations

Greg_Deckler — Fri, 24 Aug 2018 17:50:15 GMT

There have been a lot of posts over the years about how to use RANKX to create a measure, but not so many about how to use RANKX to create a measure that ranks a measure. In addition, nothing really definitive on how to aggregate measures. This post is intended to fix that.

Essentially, a measure is created via the following formula:

Measure = SUM(Table1[Column2])/SUM(Table1[Column3])

A simple approach to creating a ranking of this measure does not work very well (notice that there are 2 "Two" rows:

Measure Rank (BAD) = RANKX(ALL(Table1),[Measure])

Therefore, a slightly more complex approach is required:

Measure Rank = 
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])

For good "measure", I have included aggregations of measures, which utilize a very similar technique:

Measure StdDev = 
VAR __table = SUMMARIZE(Table1,[Column1],"__Measure",[Measure])
RETURN STDEVX.P(__table,[__Measure])

eyJrIjoiNDU2MWI3YTUtZWIxZi00MDQ0LTgzNDAtNDA5NWUxM2YyODQwIiwidCI6IjRhMDQyNzQzLTM3M2EtNDNkMi04MjdiLTAwM2Y0YzdiYTFlNSIsImMiOjN9

Re: Measure Ranking and Aggregations

aardhala — Wed, 29 Aug 2018 15:08:00 GMT

Very nice concept Greg. Definitely I would like to try it out. Thanks.

Re: Measure Ranking and Aggregations

aardhala — Thu, 30 Aug 2018 07:24:35 GMT

Hi Greg, I tried this expression out and got the Dynamic Rank calculated. But I coudn't use it as a Dimension. means, I couldn't use it on axis as a Dimension of a Bar Chart. Is there any way that we use such expression to build a dynamic Dimension ?

Re: Measure Ranking and Aggregations

Greg_Deckler — Thu, 30 Aug 2018 13:08:55 GMT

aardhala - In general, to use a measure in that way, you need to use the Disconnected Table Trick as this article demonstrates: https://community.powerbi.com/t5/Community-Blog/Solving-Attendance-with-the-Disconnected-Table-Trick/ba-p/279563

Re: Measure Ranking and Aggregations

alissa — Fri, 14 Jun 2019 20:51:40 GMT

This is awesome, and so streamlined! Could one use similar code to calculate a percentile (like PERCENTRANK in Excel)? What would that look like? I've been trying to implement something like this with DAX and it's becoming way too memory intensive.