topic Re: create a measure to output a percentile rank within multiple columns in DAX Commands and Tips

create a measure to output a percentile rank within multiple columns

ys4625 — Wed, 05 Jul 2023 18:59:43 GMT

I am trying to create a measure that will give the percentile rank within multiple columns of rent data that i have. For each property agent and within each block and property type i would like to rank the rent recieved. I intend to use this ranking to create a conditonal formating so that the relatively low rents can be seen visually when looking at the data in a matrix.

I tried to do this with RANKX and EARLIER but it doesn't seem to work in measures - can anyone help? I have attached a sample of the data to this post:

data.xlsx

Many thanks in advance

Re: create a measure to output a percentile rank within multiple columns

lbendlin — Sun, 09 Jul 2023 18:39:22 GMT

How should we interpret the duplicate values?

percentile rank

That's not really a thing, or? You want the rank, or the percentile?

Re: create a measure to output a percentile rank within multiple columns

ys4625 — Mon, 10 Jul 2023 11:26:33 GMT

Thanks so much for looking into this one!

Basicaly the data you are looking at is simulated and you can rank qrent values that are not unique with same percentile. I would like the percentile.

Thanks so much for your help!

Re: create a measure to output a percentile rank within multiple columns

lbendlin — Mon, 10 Jul 2023 12:39:14 GMT

Still not clear to me. Based on your provided sample data what would be the expected outcome?

Re: create a measure to output a percentile rank within multiple columns

ys4625 — Mon, 10 Jul 2023 20:23:54 GMT

Hi thanks for your time,

basically would like a measure that within each agent, street and property type it outputs the percentile rank. For example it would go to all the rows of agent a on street aaa with property type ii and output the percentile of the qrent for all the rows that fall in this catagory. it would do this for every agent etc.

I wrote dax query for calculated column that does this which is as follows:

PercentileRank = VAR _total = CALCULATE(DISTINCTCOUNT(property_rent[qrent]), FILTER ( property_rent, property_rent[filename] = EARLIER(property_rent[filename]) && property_rent[block] = EARLIER (property_rent[block]) && property_rent[prop_type] = EARLIER(property_rent[prop_type]) ) ) RETURN ROUND(DIVIDE ( _total + 1 - property_rent[Ranked], _total + 1 )*100, 1)

I would like this in measure form - thanks so much

Re: create a measure to output a percentile rank within multiple columns

ys4625 — Tue, 11 Jul 2023 11:14:16 GMT

Hi the expected outcome would be for all the rows of each agent, street and prop_type the measure should output what percentile each row value of qrent falls in relatively

I created a calculated column that does this which is this:

i need this to be dynamic so need it as a measure