cancel
Showing results for
Did you mean:
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.

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"
//
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
VAR H =
IF ( LEN ( Hours ) = 1,
CONCATENATE ( "0", Hours ),
CONCATENATE ( "", Hours )
)
VAR M =
IF (
LEN ( Minutes ) = 1,
CONCATENATE ( "0", Minutes ),
CONCATENATE ( "", Minutes )
)
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
Super User II

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 11
Frequent Visitor

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:

Frequent Visitor

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?

Super User II

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.

Frequent Visitor

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

Total Hours = Sum(Table[Full Duration])

Super User II

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

Frequent Visitor

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

Super User II

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:

Frequent Visitor

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

Super User II

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.

Frequent Visitor

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

Announcements

#### Microsoft Business Applications Summit sessions

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

Check out how to claim yours today!

Top Solution Authors
Top Kudoed Authors