Grouping a measure and finding the count

[Anonymous]

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 !!

Required Result
 

[v-jiascu-msft]

@Anonymous

Hi,

Maybe you missed something. These formulas should work. Please check these things below.

1. It’s better to use “AHT Hit/Miss” as a calculated column (used in a table).
2. It’s better to use “AHT HIT Count” as a measure.
3. If 1 and 2 were true, the formula of “AHT HIT Count” will be like this:

4. AHT HIT Count =
   CALCULATE ( COUNTROWS ( 'Raw Data' ), 'Raw Data'[ Hit/Miss] = "HIT" )

5. Metric 2, Metric 3, Metric 4, where are they from?

Best Regards!

Dale

[Anonymous]

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

[vanessafvg · Accepted 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" )

[vanessafvg]

@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?

[Anonymous]

@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

[vanessafvg]

@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