Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Sum Of Duration

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
 
 
Need Help
  • jdbuchanan71's avatar
    jdbuchanan71
    5 years ago

    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:

     

     

11 Replies

  • jdbuchanan71's avatar
    jdbuchanan71
    Super User
    5 years ago
    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's avatar
      Anonymous
      Not applicable
      5 years ago

      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's avatar
        jdbuchanan71
        Super User
        5 years ago

        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.