Forum Discussion

walnei's avatar
walnei
Helper III
8 years ago
Solved

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 Num...
Need Help
  • alexei7's avatar
    alexei7
    8 years ago

    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

     

     

alexei7's avatar
alexei7
Continued Contributor
8 years ago

What is the problem - which value is wrong?

walnei's avatar
walnei
Helper III
8 years ago

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.

 

 

  • alexei7's avatar
    alexei7
    Continued Contributor
    8 years ago

    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

     

     

    • walnei's avatar
      walnei
      Helper III
      8 years ago

      Thanks a lot for the help. Was really good