Sum of Time Duration Not Aggregating past 24 Hours in "hh:mm:ss" format in Power Bi Visual Table

Question

The data comes over as Duration within seconds as a data type Whole Number. I then convert this over to a "hh:mm:ss" through various calculations. I then make my new "hh:mm:ss" column into a [Sum of measure]. When I put this measure into a table it doesn't sum pass 24 hours.

Sum of Return
04:50:26
05:20:44
22:54:00
14:01:07
13:29:43
18:43:46
08:47:34
09:55:51

Total 02:03:11

Total Should be : 98:03:11 <-- This total is needed

These are the Columns

Hours = INT([duration]/3600)

Minutes = INT(MOD([duration]-([Hours]*3600),3600)/60)

Seconds = roundup(mod(mod([duration]-([Hours]*3600),3600),60),0)

VAR H = if(len([Hours])=1,CONCATENATE("0",[Hours]),CONCATENATE("",[Hours]))

VAR M = IF(LEN([Minutes])=1,CONCATENATE("0",[Minutes]),CONCATENATE("",[Minutes]))

VAR S = IF(LEN([Seconds])=1,CONCATENATE("0",[Seconds]),CONCATENATE("",[Seconds]))

RETURN = CONCATENATE([VAR H],CONCATENATE(":",CONCATENATE([VAR M],CONCATENATE(":",[VAR S]))))

Measure Formula:

Return = SUMX('Query1',[Return])

The actual total sum of the duration hours to be 98:03:11 in the visualization.

Thank you in advance.

I've edited your post to add code blocks. Can you confirm that you in fact have three columns, each named [VAR H], [VAR M], [VAR S], and a fourth named [Return]? And that [Return] is a column holding text? How do you sum a text column? — greggyb

Angelo Canepa Angelo Canepa · Accepted Answer · 2019-09-24T20:25:33

You need to divide the column seconds into the number of seconds in a day, in a new calculated column.

Hours = divide([Seconds],86400)

After that, you need to change the data type of the column to Time for the same calculated column.

Finally, you can create a Measure for summing up the hours

Hours_Sum = sum(Table[Hours])

And change the data type of the measure to hh:mm:ss

In my case, I had 16,150 seconds representing 4:29:10

The table I've used was

+---------+
| Seconds |
+---------+
|     334 |
|      96 |
|    1607 |
|    1056 |
|    1355 |
|     551 |
|    1928 |
|     767 |
|     601 |
|    1214 |
|     607 |
|    1873 |
|     519 |
|     537 |
|     686 |
|     138 |
|     439 |
|    1842 |
+---------+

Sum of Time Duration Not Aggregating past 24 Hours in "hh:mm:ss" format in Power Bi Visual Table

1 Answers