Call center, show the days where 80% of the calls was answered within 40 seconds

[Anonymous]

Hi,

After a few days of googling and trying out different formulas I hope some of you might guide me in the correct direction.

I'm working with call center data and I’m looking for a function to show the days (and months) where 80% of the calls was answered within 40 seconds.

Have any of you done something similar, or have an idea of how I can manage this?

My data looks like this:

call_id	call_dt	response_time_sec
012cefb8	2019-12-05 15:27:11.000	128
637df261	2019-12-06 13:02:55.000	99
c312ea0a	2019-12-06 07:26:45.000	1
455ac776	2019-12-05 16:32:55.000	195
137de840	2019-12-06 13:11:43.000	195
c2514e0a	2019-12-06 08:51:03.000	1
09ca1fcc	2019-12-06 09:01:11.000	88
0b141b20	2019-12-06 10:27:30.000	410
b504d0ae	2019-12-06 11:50:38.000	48
bc125d0e	2019-12-06 10:31:00.000	329
10c67cb1	2019-12-06 13:43:37.000	130
c51dab36	2019-12-06 09:01:04.000	188
4a182da4	2019-12-06 07:02:05.000	1
13f947cf	2019-12-06 13:14:11.000	97
cc16e377	2019-12-05 11:13:28.000	155

[az38 · Accepted solution]

Hi @Anonymous 

try new table

TableByDay = summarize('Table';'Table'[call_dt].[Date];"Count";countrows('Table');"countx";countrows(filter('Table';[response_time_sec]<=40));"InTime";divide(countrows(filter('Table';[response_time_sec]<=40));countrows('Table')))

"InTime" field will show you a % of the calls was answered within 40 seconds by day

to summarize by month

TableByMonth = summarize('Table';'Table'[call_dt].[Year];'Table'[call_dt].[Month];"Count";countrows('Table');"countx";countrows(filter('Table';[response_time_sec]<=40));"InTime";divide(countrows(filter('Table';[response_time_sec]<=40));countrows('Table')))

do not hesitate to give a kudo to useful posts and mark solutions as solution

[az38 · Accepted solution]

Hi @Anonymous 

try new table

TableByDay = summarize('Table';'Table'[call_dt].[Date];"Count";countrows('Table');"countx";countrows(filter('Table';[response_time_sec]<=40));"InTime";divide(countrows(filter('Table';[response_time_sec]<=40));countrows('Table')))

"InTime" field will show you a % of the calls was answered within 40 seconds by day

to summarize by month

TableByMonth = summarize('Table';'Table'[call_dt].[Year];'Table'[call_dt].[Month];"Count";countrows('Table');"countx";countrows(filter('Table';[response_time_sec]<=40));"InTime";divide(countrows(filter('Table';[response_time_sec]<=40));countrows('Table')))

do not hesitate to give a kudo to useful posts and mark solutions as solution

[Anonymous]

Thanks @az38!

If I would like to use a what if instead of hardcoding the values, would that be possible? When I tried it's just using the default value and not the one set by the slider.

[az38]

sorry @Anonymous  do not understand your new idea. could you demonstrate desired result?

[Anonymous]

Sorry for the bad description. 

So in the solution you posted the value 40 is hardcoded twice. Instead of using this value I tried to use a what if and use that value in the formula. What happens is that the formula is using the default value provided when creating the what if, instead of using the dynamic value. 

So I created a new what if called wait_time_threshold with a default value of 40. Then I used the formula you provided and replaced 40 with wait_time_threshold[wait_time_threshold Value].

I also added a new column thr which should contain the wait_time_threshold[wait_time_threshold Value]. 

TableByDay = summarize(event;event[start_dt].[Date];"Count";countrows(event);"countx";countrows(filter(event;event[wait_time]<=wait_time_threshold[wait_time_threshold Value]));"InTime";divide(countrows(filter(event;event[wait_time]<=wait_time_threshold[wait_time_threshold Value]));countrows(event));"thr";wait_time_threshold[wait_time_threshold Value])

If I put all of this in a table it looks like this

As you can see the thr column, which should be the value from the what if only contains the default value of 40.

[az38]

@Anonymous but in total of tr column is a too big value.

try to set thr column as a calculate. it looks like some aggregation

not sure, but for example

"thr";calculate(min(wait_time_threshold[wait_time_threshold Value]))

do not hesitate to give a kudo to useful posts and mark solutions as solution

[Anonymous]

Thanks again @az38, but that gives an error "Column 'wait_time_threshold Value' in table 'wait_time_threshold' cannot be found or may not be used in this expression."

[az38]

@Anonymous 

share your pbix-file with data example

do not hesitate to give a kudo to useful posts and mark solutions as solution

[Anonymous]

Thanks @az38 

Download:  https://filebin.net/es1jtgtq3l3ljm3u/example.pbix?t=ote7tp35

Edit: I see that Filebin renamed the file extension to .zip, but it should work by renaming it to .pbix

[az38 · Accepted solution]

Hi @Anonymous 

its better to create measure in the TableByDay

InTimeMeasure = countrows(FILTER(example;and('example'[response_time_sec]<=[wait_time_threshold Value];example[call_dt].[Date]=SELECTEDVALUE(TableByDay[Date])))) / countrows(FILTER(example;example[call_dt].[Date]=SELECTEDVALUE(TableByDay[Date])))

do not hesitate to give a kudo to useful posts and mark solutions as solution

[Anonymous]

Thanks @az38, that solved all my problems 🙂 

[az38]

@Anonymous  Im happy for you if this report was the only your problem 🙂 🙂 Good luck!

do not hesitate to give a kudo to useful posts and mark solutions as solution

LinkedIn