How to handle the call duration column?

[bourne2000]

Hi 

I have a column called Talk time. This is call centre data

When I tried to convert this column into duration, I am getting below

I want to calculate total talk time. When I calculated it was not coming properly.

Can anyone advise how to calculate correctly? Am I doing anything wrong?

[v-luwang-msft · Accepted solution]

Hi @bourne2000 ,

You can follow these steps to calcualte:

1. Change the data type of duration column to HH:MM:SS:

Then create new column:

SumTalkTime = 
VAR TotalSeconds =
    SUMX (
        'Table',
        HOUR ( 'Table'[Talktime] ) * 3600
            + MINUTE ( 'Table'[Talktime] ) * 60
            + SECOND ( 'Table'[Talktime] )
    )
VAR Days =
    TRUNC ( TotalSeconds / 3600 / 24 )
VAR Hors =
    TRUNC ( ( TotalSeconds - Days * 3600 * 24 ) / 3600 )
VAR Mins =
    TRUNC ( MOD ( TotalSeconds, 3600 ) / 60 )
VAR Secs =
    MOD ( TotalSeconds, 60 )
RETURN
    IF ( DAYS = 0, "", IF ( DAYS > 1, DAYS & "days ", Days & "day" ) )
        & IF ( Hors < 10, "0" & Hors, Hors ) & ":"
        & IF ( Mins < 10, "0" & Mins, Mins ) & ":"
        & IF ( Secs < 10, "0" & Secs, Secs )

Get:

Power bi not support duration type, I'd like to suggest you convert it as total second or other similar numeric value to calculate.
You can also take a look at below blog to know how to handle with duration in power bi:

Aggregating Duration/Time (Both Excel and DAX lack support for a true time duration data type making the display of duration in a format such as “HH:MM:SS” problematic. However, with some creative formulas, this problem can be solved.）

And similar question refer:

https://community.powerbi.com/t5/Desktop/duration-format/m-p/2180972 

Did I answer your question? Mark my post as a solution!

Best Regards

Lucien

[amitchandak]

@bourne2000 , Sum operation do work on duration. Refer these work arounds

Duration
https://radacad.com/calculate-duration-in-days-hours-minutes-and-seconds-dynamically-in-power-bi-usi...
https://social.technet.microsoft.com/wiki/contents/articles/33644.powerbi-aggregating-durationtime-i...
https://www.pbiusergroup.com/communities/community-home/digestviewer/viewthread?GroupId=547&MessageK...