Summation of HH:MM:SS entries to value greater than 24 hours

[tobyg]

Hi everyone,

I have a question regarding the summation of HH:MM:SS fields. In my file, I have multiple rows with time entries in this format and would like to filter and sum them. This works, but is limited to the 24 hours. For example, if i filter for "azure a", "azure b" and "database", the result would be 02:00:00 and not 26:00:00. How can this be achieved? I already read posts regarding a date or a time in seconds, but couldn't find any with HH:MM:SS.

This is the DAX query i have been using:

time = FORMAT(CALCULATE(SUM('table'[Time]), 
FILTER('table', 'table'[Category] IN {"azure 1", "azure 2", "database"})), "HH:MM:SS")

This is the post I meant 

Calculating time that has more than 24 hours in a format (h):mm:ss 

Is there any way to "easily" sum the fields, or do i have to transform the HH:MM:SS fields to seconds in a calculated column, then sum them and in the end transform them back into HH:MM:SS? Any other suggestions are welcome. Thanks!

[mahoneypat · Accepted solution]

This is a bit of a hack, but it works.  You can make a measure using SUMX.  If you have non-zero values for minutes and seconds, you can add additional variables called minutes and seconds with SUMX and the MINUTE and SECOND functions and use them in the return too.

Time Sum = var hours = SUMX('Time', HOUR('Time'[Time]))

return hours & ":00:00"

If you want to returns number of hours for visuals, etc. just leave off the ":00:00".

Regards,

Pat

[tobyg]

Hi Pat,

thanks for the response and approach with the SUMX! I actually needed the sum with minutes included, so I searched and found the answer in an older powerbi post (i must have googled wrong and couldnt find it at first). 

This is the final query structure I used:

total_time = 
VAR totalsecs = SUMX(FILTER('table', 'table'[Category] IN {"azure a", "azure b", "database"}), HOUR('table'[Time])*3600 + MINUTE('table'[Time])*60 + SECOND('table'[Time]))
var hours = TRUNC(totalsecs/3600)
var minutes = TRUNC(MOD(totalsecs, 3600) / 60)
var seconds = MOD(totalsecs, 60)
RETURN hours & ":" & IF(minutes<10, "0"&minutes, minutes) & ":" & IF(seconds<10, "0"&seconds, seconds)