Calculate Per hour call rate by caller

[ashishoza12345]

Hi All,

Can anyone please help me with creating a measure where I have to caculate number of calls by a caller per hour.
I have the dataset in given below format:

Let me know if we can create a calculated column where for Example; Based on the data, on 1st Jan John Smith has worked for 4 hours (09:30 am - 1:30 pm) and Paul Clarke has worked for 2.5 hours (9:20 am to 11:50 am) and the calls that were made on that day were 6, then the calls per hours will be 6.5/6=1.08

Please help!

Thanks in advance!

[ashishoza12345]

Hi @v-cgao-msft ,

That was a great help.
This technique did help me for what I was looking for.

Appreciate your work and Thank you once again! 

[v-cgao-msft]

Hi，

If I have posted a response that resolves your question,  then please consider Accept it as the solution to help the other members find it more quickly.  Thanks in advance.😊

Best Regards,

Community Support Team_Gao

[v-cgao-msft · Accepted solution]

Hi @ashishoza12345 ，

Split Call Intervall column by delimiter in PowerQuery Editor

New table.

Table 2 = SUMMARIZE('Table','Table'[Date],'Table'[Name],"start time",MIN('Table'[Call Interval Start]),"end time",MAX('Table'[Call Interval End]))

Try this column.

times = DATEDIFF('Table 2'[start time],'Table 2'[end time],SECOND)/3600

Try this measure.

the calls per hours1 = 
VAR _dayhours = CALCULATE(COUNT('Table'[Number of Calls]),'Table'[Date]=MAX('Table 2'[Date]))
VAR _totalphonehours = CALCULATE(SUM('Table 2'[times]),ALLEXCEPT('Table 2','Table 2'[Date]))
RETURN
_totalphonehours/_dayhour

Put measure into visual，the result should be like this.

Attach the pbix file for reference. Hope it helps.

Best Regards,
Community Support Team_Gao

If there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!