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

Grow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.

Reply
walnei
Helper III
Helper III

AVERAGE TIME

Hello could you help me with the following measure. I want to calculate the average time of each lap of the pilots according to print. I have a Total Time measurement and another Measure with the Number of laps of each pilot. I can not calculate the average time for each lap. Time Total / Laps, the values ​​come as integer and not as time.

Sem título.png

 

 

1 ACCEPTED SOLUTION
alexei7
Continued Contributor
Continued Contributor

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

 

 

View solution in original post

8 REPLIES 8
alexei7
Continued Contributor
Continued Contributor

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

alexei7
Continued Contributor
Continued Contributor

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.

Now there is another problem that I can not solve. There are hours after 24 hours so the value goes wrong. How can I add hours over 24 hours. As the example of print. Thank you for your help.

 

Sem título.png

alexei7
Continued Contributor
Continued Contributor

What is the problem - which value is wrong?

When the total time passes 24 hours it will reset the value and start again. Example, if you have 25 hours it seems like 1 hour, if you have 28 hours it will appear 4 hours.

Sem título.png

 

 

alexei7
Continued Contributor
Continued Contributor

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

 

 

Thanks a lot for the help. Was really good

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.