Skip to main content
cancel
Showing results for 
Search instead 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

Reply
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?

 

seconds to hours.JPG

 

 

 

 

1 ACCEPTED SOLUTION
Icey
Community Support
Community Support

Hi @Anonymous ,

 

How about this?

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.

View solution in original post

9 REPLIES 9
Icey
Community Support
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"
        )
    )

measures.PNG

 

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:

 

measure total calls.JPGexcel total call.JPG

Icey
Community Support
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"
            )
    )

second.PNG

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

 

measure.JPG

Icey
Community Support
Community Support

Hi @Anonymous ,

 

How about this?

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

parry2k
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"

 

 

Additional code

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

 

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors