Having trouble with percentiles!

[DavidData]

Hello everyone, I'm having a bit of trouble making an idea work, I want one column that classifies a list of "agents" in different quartiles(Q1,Q2,Q3,Q4), I've read all around to forum but I just cant get it to work.

My data sources are:
A) "DatosAgentes" with all the personal info from the agents

B) "bruto_llamadas", that connects to MySQL, inside B is a list of calls, with the AHT(Average Handling Time) and the Agent identifier or "Login", 

Example data:

A: 

LOGIN	AGENT
76000	DAVID
76001	CARLOS
76002	MARIO
76003	ARANTXA
76004	MARIA
76005	MACARENA

B)

UCID(UNIQUE IDENTIFIER)	AGENT	AHT	DATE
CALL NUMBER 1	76001	485	04/11/2020
CALL NUMBER 2	76007	232	18/10/2020
CALL NUMBER 3	76003	339	31/08/2020
CALL NUMBER 4	76000	143	27/10/2020
CALL NUMBER 5	76003	480	28/08/2020
CALL NUMBER 6	76002	234	11/09/2020
CALL NUMBER 7	76000	248	06/11/2020
CALL NUMBER 8	76005	379	10/09/2020
CALL NUMBER 9	76002	412	17/10/2020
CALL NUMBER 10	76000	314	17/09/2020
CALL NUMBER 11	76007	452	14/09/2020
CALL NUMBER 12	76006	242	28/08/2020
CALL NUMBER 13	76005	481	16/08/2020
CALL NUMBER 14	76002	301	08/09/2020
CALL NUMBER 15	76007	374	13/08/2020
CALL NUMBER 16	76003	190	08/08/2020
CALL NUMBER 17	76001	226	06/10/2020
CALL NUMBER 18	76001	145	19/10/2020
CALL NUMBER 19	76007	203	22/08/2020
CALL NUMBER 20	76003	201	04/11/2020
CALL NUMBER 21	76000	301	06/09/2020
CALL NUMBER 22	76007	183	04/09/2020
CALL NUMBER 23	76001	167	07/10/2020
CALL NUMBER 24	76000	439	07/09/2020

Inside (A) I calculate the average AHT(and even more KPIs) with this formula "CALCULATE(AVERAGE(bruto_llamadas[TMO-1]),ALL(bruto_llamadas[TMO-1]))" inside TMO-1 are only the call durantions from the current month.

My desired result is like this:

AGENT	AHT(AVG)	Q
DAVID	289	1
CARLOS	256	1
MARIO	316	3
ARANTXA	303	2
MARIA	0	-
MACARENA	430	4

I want to classify the agents in 4 different Q depending on their average TMO-1.

Can you help me?

Thanks.

[littlemojopuppy]

@DavidData download the file again from the link above and take a look 🙂

[littlemojopuppy]

That was my bad...put the table inside the ALL() function.

So to get to the desired output you're going to need another measure that looks at an agent's AHT and compares it to the quartile values and then assigns Q1, Q2, etc.  Something along the lines of this...

SWITCH()
	TRUE(),
	[Agent AHT] < [1st Quartile], "Q1",
	AND
		[Agent AHT] >= [1st Quartile],
		[Agent AHT] < [2nd Quartile
	), "Q2",
	AND
		[Agent AHT] >= [2nd Quartile],
		[Agent AHT] < [3rd Quartile
	), "Q3",
	AND
		[Agent AHT] >= [3rd Quartile],
		[Agent AHT] < [4th Quartile
	), "Q4",
	BLANK()
)

[DavidData]

Sorry, maybe I've explained wrongly. I know I will have to compare each agent's aht with the different Q inside the table, but the problem is that when I calculate the quartiles the calculation is made inside each agent.

[littlemojopuppy · Accepted solution]

You're looking for this???

You can download the Excel file...the measures are in Power Pivot.

Editing post acceptance to include the measures:

Agent Average Handling Time:=AVERAGEX(
	Agents,
	[Average Handling Time]
)


Agent 1st Quartile:=PERCENTILEX.INC(
	ALL(Agents),
	[Agent Average Handling Time],
	.25
)


Agent Quartile Bucket:=SWITCH(
		TRUE(),
		[Agent Average Handling Time] <= [Agent 1st Quartile], "1st",
		AND(
			[Agent Average Handling Time] > [Agent 1st Quartile],
			[Agent Average Handling Time] <= [Agent 2nd Quartile]
		), "2nd",
		AND(
			[Agent Average Handling Time] > [Agent 2nd Quartile],
			[Agent Average Handling Time] <= [Agent 3rd Quartile]
		), "3rd",
		AND(
			[Agent Average Handling Time] > [Agent 3rd Quartile],
			[Agent Average Handling Time] <= [Agent 4th Quartile]
		), "4th",
		BLANK()
	)

[DavidData]

EDIT: Sorry, didnt check the file, I'm checking how you've made it work in excel, didnt even know you could do that kind of formula inside Excel. Thanks

Okey... so.. its partly working, it does keep the same value between agents, so thats a plus, but now one thing I didnt specify at the beginning, I do apply filters to the data, like what service does the call come from and I need the Q to respect the filters,

From what I've seen so far it calculates the quartiles of all the calls, not just the ones I've got selected in the filter. How can I make it work like that?

[littlemojopuppy]

Hi!  I didn't see this response until now.  Apologize for the delay 😔

The percentile calculations use the ALL function in them.  Change ALL to ALLEXCEPT and include the fields you want to filter by.  For example ALLEXCEPT('Handing Times', [Date])

[DavidData]

Sorry to bother you again, now it IS calculating with the correct data(thanks for the tip with allexcept, its working!) but I dont know why the number of agents per Q is not the same, when doing percentiles, well, quartiles, all 4 of them have to be the same size.

If you need me to send you the file or to screenshot all the formulas.. i dont mind if the file is kept private.

Current result:

Desired result:

Have to say even if its not working right now as I wanted, thanks for all your help @littlemojopuppy and patience, I come from an only excel enviroment and I've been trying with curses to make PowerBI work for me...

[littlemojopuppy]

Ok...the calculations were dividing the calls into percentile buckets based on time, not the agents...let me modify calcs.  Will let you know when you can download the file again.

[littlemojopuppy]

Unless I'm missing something, wouldn't this work?

PERCENTILEX.EXC(
Agents,
[Average AHC],
-- First Quartile, modify as appropriate for others
.25
)

https://docs.microsoft.com/en-us/dax/percentilex-exc-function-dax

[DavidData]

Thats what I though, but when I input that formula it displays the first quartile for each agent. (Cuartil = Q)