Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
113 | |
99 | |
75 | |
73 | |
49 |
User | Count |
---|---|
145 | |
109 | |
109 | |
90 | |
64 |