Measure to score based on quartiles.

[GeorgeEard]

Hi,

I would like to make a measure that scores 'Partners' on the count of their 'agreements'. The partners in the top quartile, with the highest number of agreements get a score of 12, the quartile under gets 5, under that 1 and then 0.

I have tried:

IF(
Aggr(count([Agreements), [Partner]) >= Fractile(TOTAL [Agreements], 0.75), 12,
IF(
Aggr(count([agreements), [Partner]) >= Fractile(TOTAL [Agreements], 0.50), 5,
IF(
Aggr(count(Agreements), [Partner]) >= Fractile(TOTAL [agreements], 0.25), 1,
0
)
)
)

However this has not worked.

Thank you!

[JandreKillianRIC · Accepted solution]

Hi @GeorgeEard 

This is the correct expression to use. 

IF(
Aggr(Count(Agreement), [Partner]) >= Fractile(Total Aggr(Count(Agreement), Partner), 0.75), 12,
IF(
Aggr(Count(Agreement), [Partner]) >= Fractile(Total Aggr(Count(Agreement), Partner), 0.50), 5,
IF(
Aggr(Count(Agreement), [Partner]) >= Fractile(Total Aggr(Count(Agreement), Partner), 0.25), 1,
0
)
)
)

[GeorgeEard]

Thanks so much!