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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

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
July 2024 Power BI Update

Power BI Monthly Update - July 2024

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

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.