Sum Of Duration

[Anonymous]

hi all

pretty new to power bi, and im struggling with handling duration, i want to be able to use the total duration of all rows as a sum value.

i can sum the total duration into seconds and have tried to aggregate the data vie the use of DAX

Duration =

// Duration formatting

// * @konstatinos 1/25/2016

// * Given a number of seconds, returns a format of "hh:mm:ss"

//

// We start with a duration in number of seconds

VAR Duration = [Seconds]

// There are 3,600 seconds in an hour

VAR Hours =

INT ( Duration / 3600)

// There are 60 seconds in a minute

VAR Minutes =

INT ( MOD( Duration - ( Hours * 3600 ),3600 ) / 60)

// Remaining seconds are the remainder of the seconds divided by 60 after subtracting out the hours

VAR Seconds =

ROUNDUP(MOD ( MOD( Duration - ( Hours * 3600 ),3600 ), 60 ),0) // We round up here to get a whole number

// These intermediate variables ensure that we have leading zero's concatenated onto single digits

// Hours with leading zeros

VAR H =

IF ( LEN ( Hours ) = 1,

CONCATENATE ( "0", Hours ),

CONCATENATE ( "", Hours )

)

// Minutes with leading zeros

VAR M =

IF (

LEN ( Minutes ) = 1,

CONCATENATE ( "0", Minutes ),

CONCATENATE ( "", Minutes )

)

// Seconds with leading zeros

VAR S =

IF (

LEN ( Seconds ) = 1,

CONCATENATE ( "0", Seconds ),

CONCATENATE ( "", Seconds )

)

// Now return hours, minutes and seconds with leading zeros in the proper format "hh:mm:ss"

RETURN

CONCATENATE (

H,

CONCATENATE ( ":", CONCATENATE ( M, CONCATENATE ( ":", S ) ) )

)

but the output of the custom column will not let me set it as sum, ive been trying to resolve this for about 10 hours now am i missing something as this seems such a dificult task to resolve which should be simple

[Anonymous]

@jdbuchanan71 thank for your help, 

[jdbuchanan71]

Full Duration =  
VAR _Seconds = [Duration Seconds]
VAR _Minutes = INT ( DIVIDE ( _Seconds, 60 ) )
VAR _RemainingSeconds = MOD ( _Seconds, 60 )
VAR _Hours = INT ( DIVIDE ( _Minutes, 60 ) )
VAR _RemainingMinutes = MOD ( _Minutes, 60 )

RETURN
    IF (
        NOT ISBLANK ( [Duration Seconds] ),
            FORMAT ( _Hours, "00" ) & ":" & 
            FORMAT ( _RemainingMinutes, "00" ) & ":" & 
            FORMAT ( _RemainingSeconds, "00" )
    )

If you make this measure it will give you an output that looks like this:

[Anonymous]

thank you four your reply

this is what i get on the data tab, which is great, but as soon as i drag that column into the value on the graph, it still sets it as count, how can i set this to sum?

[jdbuchanan71]

Don't make it a calcualted column, make it just a measure.  You will also need a measure that sums your 'Table'[Seconds].

Duration Seconds = SUM ( 'Table'[Seconds] )

This is what goes into the firs VAR on the other measure.

[Anonymous]

Do i need to sum the [full duration] aswell?

Total Hours = Sum(Table[Full Duration])

[jdbuchanan71]

Does your Table[Seconds] column include the hours?  I assume it does so no, you would just need to sum the seconds.

[Anonymous]

i must be missing somethign here, i just cannot get this to work

so, just to clarify what ive done

i have created a column in power query 

Seconds = [EndTime]-[StartTime] set this to total seconds

in DAX

Duration Seconds = Sum(Table[Seconds])

In DAX

Duration =

VAR _Seconds = [Duration Seconds]

VAR _Minutes = INT ( DIVIDE ( _Seconds, 60 ) )

VAR _RemainingSeconds = MOD ( _Seconds, 60 )

VAR _Hours = INT ( DIVIDE ( _Minutes, 60 ) )

VAR _RemainingMinutes = MOD ( _Minutes, 60 )

RETURN

IF (

NOT ISBLANK ( [Seconds] ),

FORMAT ( _Hours, "00" ) & ":" &

FORMAT ( _RemainingMinutes, "00" ) & ":" &

FORMAT ( _RemainingSeconds, "00" )

)

but when i drop duration column in to value it sets it as count.

EDIT

just check the data tab and this is how the data looks

[jdbuchanan71 · Accepted solution]

Do not add these as a column in your table.  Make them measures only.

Then you add the measure to a visual next to the EvenName:

[Anonymous]

ok, ive se them up as a measure but it wont let me add it to the value field in the graph

[jdbuchanan71]

Sorry, I think I understand now.  You want to show the hh:mm:ss on the axis of the graph, circled in red below:

Unfortunately the graph you will only accept numbers in the value and there is not a way to show a duration format on the axis.  About the best you could do is add it as a tooltip.

[Anonymous]

thnak for the reply, yeh i worked that out, thank you for your help