Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Anonymous
Not applicable

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

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.000128
637df261 2019-12-06 13:02:55.00099
c312ea0a 2019-12-06 07:26:45.0001
455ac776 2019-12-05 16:32:55.000195
137de840 2019-12-06 13:11:43.000195
c2514e0a 2019-12-06 08:51:03.0001
09ca1fcc 2019-12-06 09:01:11.00088
0b141b20 2019-12-06 10:27:30.000410
b504d0ae 2019-12-06 11:50:38.00048
bc125d0e 2019-12-06 10:31:00.000329
10c67cb1 2019-12-06 13:43:37.000130
c51dab36 2019-12-06 09:01:04.000188
4a182da4 2019-12-06 07:02:05.0001
13f947cf 2019-12-06 13:14:11.00097
cc16e377 2019-12-05 11:13:28.000155
2 ACCEPTED SOLUTIONS
az38
Community Champion
Community Champion

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

 


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

View solution in original post

az38
Community Champion
Community Champion

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

 


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

View solution in original post

11 REPLIES 11
az38
Community Champion
Community Champion

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

 


do not hesitate to give a kudo to useful posts and mark solutions as solution
LinkedIn
Anonymous
Not applicable

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
Community Champion
Community Champion

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


do not hesitate to give a kudo to useful posts and mark solutions as solution
LinkedIn
Anonymous
Not applicable

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

Capture2.PNG

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

az38
Community Champion
Community Champion

@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


do not hesitate to give a kudo to useful posts and mark solutions as solution
LinkedIn
Anonymous
Not applicable

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
Community Champion
Community Champion

@Anonymous 

share your pbix-file with data example

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


do not hesitate to give a kudo to useful posts and mark solutions as solution
LinkedIn
Anonymous
Not applicable

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
Community Champion
Community Champion

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

 


do not hesitate to give a kudo to useful posts and mark solutions as solution
LinkedIn
Anonymous
Not applicable

Thanks @az38, that solved all my problems 🙂 

az38
Community Champion
Community Champion

@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


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

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors