Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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
Solved! Go to Solution.
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:
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:
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?
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
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:
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:
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