Forum Discussion
AVERAGE TIME
Hi walnei,
I found it quite hard to replicate your data as my Power BI didnt like times that went beyond 24 hours, however i've come up with a solution that works for me. There may be a simpler way of doing this, so someone else may be able to help with this
This post was helpful to me.
Create the following custom columns:
1: Seconds = right(Table1[TIME TOTAL],2)
2: Minutes = mid(Table1[TIME TOTAL],4,2)
3. Hour = left(Table1[TIME TOTAL],2)
4. Transfertoseconds = Table1[Hour]*60*60+Table1[Minutes]*60+Table1[Seconds]
5. NewTime = FORMAT(Table1[Transfertoseconds]/86400,"Short Time")
And then your "TEMP MED LAPS" just uses the NewTime calculation, so:
TEMP MED LAPS = FORMAT(DIVIDE(sum(Table1[NewTime]),sum(Table1[LAPS])), "HH:MM:SS")
Replacing the emoji as per my other comment.
Hope that helps,
Alex
Hi walnei,
I think you need to use the "FORMAT" function. My formula worked for me:
TEMP MED LAPS =
FORMAT (
DIVIDE ( SUM ( Table1[TIME TOTAL] ), SUM ( Table1[LAPS] ) ),
"HH:MM:SS"
)You'll want to change this depending on your own table and column/measure names.
edit: the emoji should be replaced with a colon and then "S" (i.e. ": S" without the space) - the whole thing should be (again without the space)
"HH:MM: SS"
Hope that helps,
Alex
Very good. Could you just help me with the following detail. The total time fields and laps are a metric. How do I adjust this in the formula you passed since it takes into account that these measures were a column. thank you
You should just be able to replace the
SUM ( Table1[TIME TOTAL]
and
SUM ( Table1[LAPS] )
bit of the formula with whatever your metrics are called.