cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Stuartroberts
Frequent Visitor

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.

Stuartroberts_0-1618628934522.png

 

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
 
 
1 ACCEPTED SOLUTION

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

jdbuchanan71_0-1618635615608.png

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

jdbuchanan71_1-1618635675396.png

 

 

View solution in original post

11 REPLIES 11
Stuartroberts
Frequent Visitor

@jdbuchanan71 thank for your help, 

jdbuchanan71
Super User II
Super User II

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:

 

jdbuchanan71_0-1618631249956.png

 

thank you four your reply

 

Stuartroberts_0-1618631547938.png

 

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?

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.

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

 

Total Hours = Sum(Table[Full Duration])

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

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
 
Stuartroberts_0-1618635039343.png

 

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

jdbuchanan71_0-1618635615608.png

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

jdbuchanan71_1-1618635675396.png

 

 

View solution in original post

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

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

jdbuchanan71_0-1618638117275.png

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.

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

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

MBAS Attendee Badge

Claim Your Badge & Digital Swag!

Check out how to claim yours today!

Top Solution Authors
Top Kudoed Authors