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 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.

 

 

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

     

     

8 Replies

  • alexei7's avatar
    alexei7
    Continued Contributor
    8 years ago

    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

    • walnei's avatar
      walnei
      Helper III
      8 years ago

      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's avatar
        alexei7
        Continued Contributor
        8 years ago

        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.