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 second...
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:

     

     

Anonymous's avatar
Anonymous
Not applicable
5 years ago

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's avatar
jdbuchanan71
Super User
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:

 

 

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

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

  • jdbuchanan71's avatar
    jdbuchanan71
    Super User
    5 years ago

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

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