Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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!
Solved! Go to 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.
To learn more about Power BI, follow me on Twitter or subscribe on YouTube.
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.
To learn more about Power BI, follow me on Twitter or subscribe on YouTube.
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)
User | Count |
---|---|
102 | |
88 | |
78 | |
71 | |
69 |
User | Count |
---|---|
113 | |
99 | |
97 | |
72 | |
68 |