Calculate Percentile by 2 groupings

[Anonymous]

I have a table similar to the following

Agent	|Date	|Timing(s)	|Rank
Bill	01/06/2020	115
Jane	02/06/2020	110
Sarah	02/06/2020	135
Jessica	04/06/2020	116
Jane	01/06/2020	93
Jessica	03/06/2020	133
Sarah	03/06/2020	67
Jessica	01/06/2020	113
Jane	03/06/2020	66
Bill	01/06/2020	135
Jane	04/06/2020	116
Jessica	02/06/2020	87
Sarah	03/06/2020	88
Bill	02/06/2020	47
Sarah	01/06/2020	89
Jane	04/06/2020	45
Jane	03/06/2020	101
Jessica	02/06/2020	28

I need to split the agents into 4 quartiles/Ranks based on the Timing. Ie are they between 0 to 25%, 25% to 50%, 50% to 75%, and 75% to 100%

How do I rank them into quartiles based on timings column?

Normally I would use the PERCENTILE.INC function on the timings column, but this will evaluate each row against the Percentile of the column. I want to rank each agent against the timing and the date. So where does Jane Rank on 04/06/2020 compared to her peers. Hope that makes sense.

[harshnathani · Accepted solution]

Hi @Anonymous ,

Try this measure.

This calculates percentile for each day and timings.

Percentile Club = 
var _p25 = PERCENTILEX.EXC(ALLSELECTED('Table'[Agent],'Table'[ Date]),[Total Timing],.25)
var _p50 = PERCENTILEX.EXC(ALLSELECTED('Table'[Agent],'Table'[ Date]),[Total Timing],.50)
var _p75 = PERCENTILEX.EXC(ALLSELECTED('Table'[Agent],'Table'[ Date]),[Total Timing],.75)

RETURN
SWITCH(
    TRUE(),
    [Total Timing] >=_p75 , "75% to 100%",
    [Total Timing] <_p75 && [Total Timing] >=_p50 , "50% to 75%",
    [Total Timing] <_p50 && [Total Timing] >=_p25 , "25% to 50%",
    "0% to 25%"
)

Regards,
Harsh Nathani

Appreciate with a Kudos!! (Click the Thumbs Up Button)
Did I answer your question? Mark my post as a solution!

[amitchandak]

@Anonymous , refer if these can help

percentile
https://blog.enterprisedna.co/implementing-80-20-logic-in-your-power-bi-analysis/
https://forum.enterprisedna.co/t/testing-the-pareto-principle-80-20-rule-in-power-bi-w-dax/459
https://finance-bi.com/power-bi-pareto-analysis/
https://community.powerbi.com/t5/DAX-Commands-and-Tips/Calculate-the-sum-of-the-top-80/td-p/763156