Time not adding up correctly in Total

[Anonymous]

Hi Team,

I have a measure "Project Aux" created as below and formated as hh:nn:ss.  However, this doesnt show accurate total for the hierarchy.  In the below picture, I need the total to be total hours/min/sec of the ones marked in yellow. 

How do I amend the below measure to get the right total? please help.

Project Aux =

Var PA = IFERROR((sum(ITOps_Telecom[Aux6])/60),0)

return TIME ( 0, 0, 0 )

+ DIVIDE ( PA, ( 24 * 60) )

[AlexisOlson · Accepted solution]

You've got some errors in your computations.

I'd suggest something similar to the post I linked:

Duration Formatted = 
VAR TotalDuration = SUM ( ITOps_Telecom[Aux6] )
VAR TotalHours = TRUNC ( 24 * TotalDuration )
VAR MinSec = FORMAT ( TotalDuration - TotalHours / 24, "nn:ss" )
RETURN
    TotalHours & ":" & MinSec

[AlexisOlson]

I don't think the time data type supports values greater than 24 hours, so you'll need to change this to return text values in the format you prefer.

See here for formatting suggestions: https://stackoverflow.com/questions/50388603

[Anonymous]

@AlexisOlson Thank you for the response. I did try the below.  but its not showing up the right value.

Sum of minutes formatted =

VAR sumOfMinutes =

divide (sum(ITOps_Telecom[Aux6]),(24*60))

VAR totalHours = sumOfMinutes / 60

var hours = int(sumOfMinutes)

var Mint = 60*(sumOfMinutes - hours)

var mins = int(Mint)

var secs = ROUND(60*(Mint-mins),0)

var res = hours & ":" & mins & ":" & secs

return res

[AlexisOlson · Accepted solution]

You've got some errors in your computations.

I'd suggest something similar to the post I linked:

Duration Formatted = 
VAR TotalDuration = SUM ( ITOps_Telecom[Aux6] )
VAR TotalHours = TRUNC ( 24 * TotalDuration )
VAR MinSec = FORMAT ( TotalDuration - TotalHours / 24, "nn:ss" )
RETURN
    TotalHours & ":" & MinSec

[Anonymous]

@AlexisOlson Thank you very much. I tried your solution. but it gives the values as below. 

[Anonymous · Accepted solution]

@AlexisOlson I modified your solution and it worked.  My duration is in Seconds. So I divided  sum(itops_telecom[aux6) by 86400 which is 24*60*60.

thank you very much

Duration Formatted =

VAR TotalDuration = divide (SUM ( ITOps_Telecom[Aux6] ),24*60*60)

VAR TotalHours = TRUNC ( 24 * TotalDuration )

VAR MinSec = FORMAT ( TotalDuration - TotalHours / 24, "nn:ss" )

RETURN TotalHours & ":" & MinSec

[AlexisOlson]

Great. That's exactly what I was going to suggest since my TotalDuration assumed units of days.