Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

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
Anonymous
Not applicable

@jdbuchanan71 thank for your help, 

jdbuchanan71
Super User
Super User

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

 

Anonymous
Not applicable

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.

Anonymous
Not applicable

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.

Anonymous
Not applicable

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

 

 

Anonymous
Not applicable

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.

Anonymous
Not applicable

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.