Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi ,
I'm new to Power BI.
I was trying to obtain the below bar chart for the metric- Avg. Handle Time (AHT). The snapshot for the raw data is also provided here.
The scenario is a Call center and in the raw data, i have Agent ID, Handle time per Call & Target AHT- 400 secs.
My objective is:
to obtain the # of Agents who have Not met the target (when agent's Avg. Handle Time> Target Value )
to obtain the # of Agents who have Met the target (when agent's Avg. Handle Time<= Target Value )
I created the below measure, and was able to find out whether an agent falls in to the category HIT or MISS based on their Avg. Handle Time.
AHT Hit/Miss = IF([Avg. Handle Time]>400, "MISS","HIT")
But my objective is to obtain the # of Agents who have Met or Not met the target.
I tried the below formula to find out the # of agents who have Met the target, but it's not working.
AHT HIT Count = CALCULATE(COUNTROWS('Raw Data'),(FILTER('Raw Data',[ Hit/Miss]= "HIT")))
Requesting help to sort out this issue. Thanks !!
Solved! Go to Solution.
@Anonymous
as discussed the solution is to summarize the data and then create the derived column
Summarize =
SUMMARIZE (
'Raw Data',
'Raw Data'[Agent ID],
'Raw Data'[Target AHT],
"THT", AVERAGE ( 'Raw Data'[Total Handle Time] )
)
HitMiss =
IF ( 'Summarize'[Target AHT] < 'Summarize'[THT], "Hit", "Miss" )
Proud to be a Super User!
@Anonymous
Hi,
Maybe you missed something. These formulas should work. Please check these things below.
AHT HIT Count = CALCULATE ( COUNTROWS ( 'Raw Data' ), 'Raw Data'[ Hit/Miss] = "HIT" )
Best Regards!
Dale
Hi Dale,
Sorry for the delay in reverting.
I had tried with “AHT Hit/Miss” as a calculated column, but it was considering the Handling time of agent not at an aggregated level. The initial requirement was to compare the "Avg. handling time of all the calls handled by an agent" with the Target (eg: 400) and to find out if the agent did HIT or MISS the target.
this was made possible using “AHT Hit/Miss" as a measure.
Given below is the snapshot of the table using sample data.
Now the need is to find out the # of agents who have achieved HIT (5 - in sample data)
& # of agents who have missed Target (15 - in sample data)
and in my raw data, I have other Metrics apart from AHT.
Once I sort this out with the first metric- AHT, i will use the same logic with other metrics.
Regards
Abraham Peter
@Anonymous
as discussed the solution is to summarize the data and then create the derived column
Summarize =
SUMMARIZE (
'Raw Data',
'Raw Data'[Agent ID],
'Raw Data'[Target AHT],
"THT", AVERAGE ( 'Raw Data'[Total Handle Time] )
)
HitMiss =
IF ( 'Summarize'[Target AHT] < 'Summarize'[THT], "Hit", "Miss" )
Proud to be a Super User!
@Anonymous when you say its not working, what is the issue?
fyi you dont need a filter in your calculate this should work fine (dont think that will correct the issue)
measure = CALCULATE(COUNTROWS('Raw Data'), [ Hit/Miss]= "HIT")
can you highlight your issue?
Proud to be a Super User!
@vanessafvgThanks for your tip on Filter.
Issue : Unable to get the correct count of Agents who have Met, Not met the Target.
I'm wondering if it's because of trying to Filter out a Measure & Count the # of rows.
Also please let me know, if there is a way to attach the sample .pbix file here
@Anonymous its hard to know without seeing your data to be honest, i mean if you can share you pbix that would really help
otherwise my approach would be to create 3 measures
countall = CALCULATE(COUNTROWS('Raw Data'), [ Hit/Miss]= "HIT")
counthit = CALCULATE([countall], [ Hit/Miss]= "HIT")
countmiss = CALCULATE([countall], [ Hit/Miss]= "Miss")
and then put them on a table or matrix with the AHT Hit/Miss field and Avg. Handle Time] to see whats not working properly
Proud to be a Super User!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
118 | |
104 | |
77 | |
73 | |
52 |
User | Count |
---|---|
145 | |
109 | |
109 | |
90 | |
64 |