create a measure to output a percentile rank within multiple columns

[ys4625]

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

[lbendlin]

How should we interpret the duplicate values?

 percentile rank

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

[ys4625]

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!

[lbendlin]

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

[ys4625]

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:

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 need this to be dynamic so need it as a measure

[ys4625]

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