Measure not correctly totaling for time

[shannon8000]

I created a time column using dax to convert seconds to hh:mm:ss.  From the new column is configured as text to display properly.  From there I created a measure where the time can aggregate as a total.  The Time column properly reflects the hh:mm column and is formatted as 13:30 (h:nn).  The issue is the total is not adding correctly.  It should display as 40:00.  Is there something I am missing?

[v-jiewu-msft · Accepted solution]

Hi @shannon8000 ,

Please try the following methods and check if they can solve your problem:

1.Create the simple table.

2.Create the new column to convert the seconds to hh:mm:ss column.

hh:mm:ss = 
VAR hours =
    ROUNDDOWN ( [seconds] / 3600, 0 )
VAR minutes =
    ROUNDDOWN ( MOD ( [seconds], 3600 ) / 60, 0 )
VAR seconds =
    INT ( MOD ( [seconds], 60 ) )
VAR milliseconds =
    round(MOD ( [seconds], 1 ) * 100,0)
RETURN
    FORMAT(hours,"00") & ":"
        & FORMAT(minutes, "00")
        & ":"
        & FORMAT(seconds, "00")

3.Create the new measure to calculate the time total.

Total Time = 
VAR TotalSeconds = SUMX('Table', HOUR('Table'[hh:mm:ss]) * 3600 + MINUTE('Table'[hh:mm:ss]) * 60 + SECOND('Table'[hh:mm:ss]))
RETURN FORMAT(TotalSeconds / 86400, "hh:mm")

4.Drag the measure into the table visual. The result is shown below.

Best Regards,

Wisdom Wu

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

[lbendlin]

Why 40 ?  Time is in hours and minutes.  Your data adds up to  40 hours which will display as 1 day 16 hrs  0 minutes or 16:00  (because the time format drops the day component).  It should certainly not display 23:50  - most likely your list contains other data that you are not showing.