How to calculate average and total time on Power BI?

[Anonymous]

Hello, I want to calculate the sum and average of the solution time of the help desk application requests on Power BI. However, the collection of hour information over 24 hours causes errors in average. How can I include times over 24 hours in total and average?

I would be glad if you could help.

Technician	Time
John	00:03:20
Jack	00:30:29
Jessica	01:02:42
Justin	04:01:24
Jonathan	34:02:42
Jimmy	04:03:16
Jackson	08:04:03
Jonhson	130:04:33
Jacob	67:54:34

Average Time = ?

Total Time = ?

[v-alq-msft]

Hi, @Anonymous 

Based on your description, I created data to reproduce your scenario. The pbix file is attached in the end.

Table:

You may create two measures as below.

Sum = 
var tab = 
ADDCOLUMNS(
    ALL('Table'),
    "Result",
    var f1 = SEARCH(":",[Time])
    var f2 = SEARCH(":",[Time],f1+1)
    var h = LEFT([Time],f1-1)
    var m = MID([Time],f1+1,f2-f1-1)
    var s = MID([Time],f2+1,LEN([Time])-f2)
    var totalsecond = 3600*h+60*m+s
    return
    totalsecond
)
var re = 
SUMX(
    tab,
    [Result]
)
var hh = INT(DIVIDE(re,3600))
var mm = INT(DIVIDE(MOD(re,3600),60))
var ss = INT(MOD(MOD(re,3600),60))
return
hh&":"&mm&":"&ss

Avg = 
var tab = 
ADDCOLUMNS(
    ALL('Table'),
    "Result",
    var f1 = SEARCH(":",[Time])
    var f2 = SEARCH(":",[Time],f1+1)
    var h = LEFT([Time],f1-1)
    var m = MID([Time],f1+1,f2-f1-1)
    var s = MID([Time],f2+1,LEN([Time])-f2)
    var totalsecond = 3600*h+60*m+s
    return
    totalsecond
)
var re = 
AVERAGEX(
    tab,
    [Result]
)
var hh = INT(DIVIDE(re,3600))
var mm = INT(DIVIDE(MOD(re,3600),60))
var ss = INT(MOD(MOD(re,3600),60))
return
hh&":"&mm&":"&ss

Result:

Best Regards

Allan

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Hi @v-alq-msft,

Thank you very much for the information you provide. It was really useful. However, there is one thing I cannot do. There is a time value for each request by technicians in the list here. How can I show the sum and average of these values per technician? It can be done Pivot Table on Excel, but I could not do it on Power BI. In short, I would like to show the number of requests on the basis of technicians and the average of the solution times, as seen on Pivot Table.

I would really appreciate if you can help.

Excel file in "We Transfer" 

[v-alq-msft]

Hi, @Anonymous 

I am not clear about the expected result. What is raw data like? Could you please explain furthter with some sample data? Thanks.

Best Regards

Allan

[Anonymous]

Hi, 

I couldn't manage to upload the data file here. However, in my previous post, I uploaded it to "we transfer" and shared it. The situation I am trying to explain is displayed as a pivot in the file included in the transfer.

Can you download and review the file I shared? The detail I want to explain appears in the excel file as a pivot table. The other sheet of Excel contains data.

Thank you so much.

[amitchandak]

@Anonymous , refer to these blogs on how to deal with Duration
https://radacad.com/calculate-duration-in-days-hours-minutes-and-seconds-dynamically-in-power-bi-using-dax
https://social.technet.microsoft.com/wiki/contents/articles/33644.powerbi-aggregating-durationtime-in-dax.aspx
https://www.pbiusergroup.com/communities/community-home/digestviewer/viewthread?GroupId=547&MessageKey=814a2cb4-3cca-4cd1-a620-c467adeaaaf6&CommunityKey=b35c8468-2fd8-4e1a-8429-322c39fe7110&tab=digestviewer
https://community.powerbi.com/t5/Quick-Measures-Gallery/Chelsie-Eiden-s-Duration/m-p/793639#M389