Forum Discussion
Helper IIICalculate Time Occupied Over Call during an Hour
Hello Everyone,
I'm facing an issue in defining time slot which will give me the time occupied on call during the day.
I've two consultants, one from India and other from USA.
Data View
I've time of call and the duration of call(in minutes). In very last column I've mentioned the desired time slots.
I want to calculate, on call duration %, time slot wise.
Desired table,
| Location | 1 PM | 2 PM | 3 PM | 4 PM | 5 PM | 6 PM | 7 PM |
| India | 100 % | 75 % | 100 % | 33.33% | - | - | - |
| USA | 100 % | 100 % | 83.33% | 100 % | 25 % | 100 % | 50 % |
Here is the file with Sample Data.
Thank you for providing sample data.
Your desired table outcome ignores the [Date] column - how are you planning to handle that? One result per day? Some sort of aggregation? What if a call runs into a new day?
Also, I don't see any time zone information in your setup. Is that intentional?
First step: Create a table that has all the columns and rows you want in the output:
Table = CROSSJOIN(VALUES(Call_Detail[Location]),GENERATESERIES(0,23/24,1/24))
Next step: list all the calls for the selected location.
Calls =var l = SELECTEDVALUE('Table'[Location])var t = CALCULATETABLE(Call_Detail,Call_Detail[Location]=l)return COUNTROWS(t)(the return value is just for validation purposes. We count 4 calls for India and 6 calls for USA)Next step is to generate the series for the minute numbers for each callCalls =var l = SELECTEDVALUE('Table'[Location])var t = CALCULATETABLE(Call_Detail,Call_Detail[Location]=l)var s = ADDCOLUMNS(t,"Mins",COUNTROWS(GENERATESERIES(Call_Detail[Time]*1440,Call_Detail[Time]*1440+Call_Detail[Duration(mins)]-1,1)))return CONCATENATEX(s,[Mins],",")(again CONCATENATEX is for validation purposes)
Then we need to create a similar series for each hour bucket
var h = MAX('Table'[Value])var ht = GENERATESERIES(h*1440,h*1440+59,1)And lastly we can see how many of the calls' minutes fall into the selected hour bucket, using INTERSECT()
Calls =var l = SELECTEDVALUE('Table'[Location])var ht = GENERATESERIES(max('Table'[Value])*1440,max('Table'[Value])*1440+59,1)var t = CALCULATETABLE(Call_Detail,Call_Detail[Location]=l)var s = ADDCOLUMNS(t,"Mins",COUNTROWS(INTERSECT(ht,GENERATESERIES(Call_Detail[Time]*1440,Call_Detail[Time]*1440+Call_Detail[Duration(mins)]-1,1))))return SUMX(s,[Mins])I'll leave the percentages calculation up to you.
1 Reply
- lbendlin
Super User
Thank you for providing sample data.
Your desired table outcome ignores the [Date] column - how are you planning to handle that? One result per day? Some sort of aggregation? What if a call runs into a new day?
Also, I don't see any time zone information in your setup. Is that intentional?
First step: Create a table that has all the columns and rows you want in the output:
Table = CROSSJOIN(VALUES(Call_Detail[Location]),GENERATESERIES(0,23/24,1/24))
Next step: list all the calls for the selected location.
Calls =var l = SELECTEDVALUE('Table'[Location])var t = CALCULATETABLE(Call_Detail,Call_Detail[Location]=l)return COUNTROWS(t)(the return value is just for validation purposes. We count 4 calls for India and 6 calls for USA)Next step is to generate the series for the minute numbers for each callCalls =var l = SELECTEDVALUE('Table'[Location])var t = CALCULATETABLE(Call_Detail,Call_Detail[Location]=l)var s = ADDCOLUMNS(t,"Mins",COUNTROWS(GENERATESERIES(Call_Detail[Time]*1440,Call_Detail[Time]*1440+Call_Detail[Duration(mins)]-1,1)))return CONCATENATEX(s,[Mins],",")(again CONCATENATEX is for validation purposes)
Then we need to create a similar series for each hour bucket
var h = MAX('Table'[Value])var ht = GENERATESERIES(h*1440,h*1440+59,1)And lastly we can see how many of the calls' minutes fall into the selected hour bucket, using INTERSECT()
Calls =var l = SELECTEDVALUE('Table'[Location])var ht = GENERATESERIES(max('Table'[Value])*1440,max('Table'[Value])*1440+59,1)var t = CALCULATETABLE(Call_Detail,Call_Detail[Location]=l)var s = ADDCOLUMNS(t,"Mins",COUNTROWS(INTERSECT(ht,GENERATESERIES(Call_Detail[Time]*1440,Call_Detail[Time]*1440+Call_Detail[Duration(mins)]-1,1))))return SUMX(s,[Mins])I'll leave the percentages calculation up to you.