cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Anonymous
Not applicable

## Seconds to Hours Minutes sum

Guys, I appreciate there have been lots of threads on this but i still cant seem to find a solution.  I have huge data table showing how long a certain agent has been on the phone in seconds.  We need to see in hours how long each agent is on the phone for over a weekly period.

There are loads of people trying to solve this here but i have not come acrooss a solutuion that shows hours per day with a total .  For example, in excel this can easily be done by adding a new column and divide the seconds column by 86400. Then change this to time format.  This allows you to see all the data in a table with totals at the bottom.  For example, i can see over the week this agent has been on calls for 34 hours 43 minutes. Can anyone solve this which allows me to see the eact same thing in a Power BI table?

1 ACCEPTED SOLUTION
Community Support

Hi @Anonymous ,

``````Sum Measure 2 =
VAR Seconds1 =
SUMX ( 'Calls SQL', SECOND ( 'Calls SQL'[Total Time on Calls] ) )
VAR Minutes1 =
SUMX ( 'Calls SQL', MINUTE ( 'Calls SQL'[Total Time on Calls] ) )
VAR Hours1 =
SUMX ( 'Calls SQL', HOUR ( 'Calls SQL'[Total Time on Calls] ) )
VAR Seconds2 =
MOD ( Seconds1, 60 )
VAR Minutes2 =
MOD ( TRUNC ( Seconds1 / 60 ) + Minutes1, 60 )
VAR Hours2 =
Hours1
+ TRUNC ( ( TRUNC ( Seconds1 / 60 ) + Minutes1 ) / 60 )
RETURN
Hours2 & ":"
& FORMAT ( Minutes2, "00" ) & ":"
& FORMAT ( Seconds2, "00" )
``````

Best Regards,

Icey

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

9 REPLIES 9
Community Support

Hi @Anonymous ,

Try this:

``````Measure =
SUMX ( 'table', HOUR ( 'Table'[HH:SS] ) )
+ TRUNC ( SUMX ( 'table', MINUTE ( 'Table'[HH:SS] ) ) / 60 ) & ":"
& MOD ( SUMX ( 'table', MINUTE ( 'Table'[HH:SS] ) ), 60 )
& (
IF (
LEN ( MOD ( SUMX ( 'Table', MINUTE ( 'Table'[HH:SS] ) ), 60 ) ) > 1,
":00",
"0:00"
)
)``````

Best Regards,

Icey

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

Anonymous
Not applicable

Thanks but this doesnt seem to be calcualting correctly. Power BI is slightly out compared to excel.  So the result should be 03:50:44 but power BI is showing 03:39:00.  See examples below:

Community Support

Hi @Anonymous ,

Sorry, the previous measure is a bit problematic. Try this:

``````Sum Measure 2 =
SUMX ( 'Calls SQL', HOUR ( 'Calls SQL'[Total Time on Calls] ) )
+ TRUNC ( SUMX ( 'Calls SQL', MINUTE ( 'Calls SQL'[Total Time on Calls] ) ) / 60 ) & ":"
& FORMAT (
MOD ( SUMX ( 'Calls SQL', MINUTE ( 'Calls SQL'[Total Time on Calls] ) ), 60 )
+ TRUNC ( SUMX ( 'Calls SQL', SECOND ( 'Calls SQL'[Total Time on Calls] ) ) / 60 ),
"00" & ":"
& FORMAT (
MOD ( SUMX ( 'Calls SQL', SECOND ( 'Calls SQL'[Total Time on Calls] ) ), 60 ),
"00"
)
)
``````

The total result in Excel is also 3:50:34.

Best Regards,

Icey

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

Anonymous
Not applicable

No need to be sorry i appreciate all your help with this.  Its works almost perfrectly except i noticed the following difference.

Power BI shows 163 minutes rather than 43

Community Support

Hi @Anonymous ,

``````Sum Measure 2 =
VAR Seconds1 =
SUMX ( 'Calls SQL', SECOND ( 'Calls SQL'[Total Time on Calls] ) )
VAR Minutes1 =
SUMX ( 'Calls SQL', MINUTE ( 'Calls SQL'[Total Time on Calls] ) )
VAR Hours1 =
SUMX ( 'Calls SQL', HOUR ( 'Calls SQL'[Total Time on Calls] ) )
VAR Seconds2 =
MOD ( Seconds1, 60 )
VAR Minutes2 =
MOD ( TRUNC ( Seconds1 / 60 ) + Minutes1, 60 )
VAR Hours2 =
Hours1
+ TRUNC ( ( TRUNC ( Seconds1 / 60 ) + Minutes1 ) / 60 )
RETURN
Hours2 & ":"
& FORMAT ( Minutes2, "00" ) & ":"
& FORMAT ( Seconds2, "00" )
``````

Best Regards,

Icey

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

Anonymous
Not applicable

That is perfect thank you so much for your help

Super User
Super User

@Anonymous did you looked at similar post here.

Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤

Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

Anonymous
Not applicable

Just read it and thanks for this.  I have added the following but cant seemt to get the addtional code below to work properly.  Can you help add the ------if(len(mod etc--------------- to my code below:

Measure1 = sumx('Calls SQL', hour('Calls SQL'[Out Time])) + TRUNC(sumx('Calls SQL', MINUTE('Calls SQL'[Out Time]))/60) & ":" & mod(sumx('Calls SQL', MINUTE('Calls SQL'[Out Time])), 60) & ":00"

`IF(len(mod(sumx(Base_Pautas; MINUTE('table', MINUTE('table'[time])); 60))>1;":00";"0:00")`