Forum Discussion

sfernamer's avatar
sfernamer
Helper III
3 years ago

Average time grouped by column

Hi everyone!

 

I'm working with time (minutes and seconds played) and would like to get the average time for every distinct pair of players. I tried the following formula but didn't work because it's said that the variable can't get an unique value.

 

How could I get the average value? I add a Drive link to the Excel file with the Raw Data and the expected result, to try to help to get the result.

 

Link with the Raw Data and Expected Result: Link with file 

 

Thank you in advance.

 

Tested measure (not working):

 

 Average Time = 
 var IntCourt = 'Table'[Int_OnCourt]

Return
AVERAGEX( 
    FILTER(ALL('Table'), 'Table'[Int_OnCourt] = IntCourt),
          'Table'[TimeOnCourt])

 

Need Help

6 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    3 years ago

    sfernamer , Try like

     

    Avg time =
    var _time = AVERAGEX(Table,HOUR(Table[TimeOnCourt])*3600+Minute(Table[TimeOnCourt])*60 +SECOND(Table[TimeOnCourt]))
    return
    time(QUOTIENT(_time,3600) ,QUOTIENT(Mod(_time,3600),60),mod(Mod(_time,3600),60))

    • sfernamer's avatar
      sfernamer
      Helper III
      3 years ago

      Thank you for your time, amitchandak , but this formula didn't work for my case. The one taht worked more or less is the one posted for the other user. Thank you anyway for your help and your time. 🙂

    • sfernamer's avatar
      sfernamer
      Helper III
      3 years ago

      Hi Ahmedx 

      Your answer worked for almost all cases but there are cases where the result is "00:00:60" instead of "00:01:00", Idk why is not rounding this number but, if I wanna convert the text result into time, it's not working for these cases. For the other ones, it's working perfectly. 

      • Ahmedx's avatar
        Ahmedx
        Super User
        3 years ago

        you can create an example where the measure counts incorrectly?