Forum Discussion
sfernamer
Helper III
Helper IIIAverage 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...
sfernamerHelper III
Helper IIIHi 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
Super User
Super User
Average TimeOnCourt2 =
VAR _Count = COUNTROWS('RawData')
VAR _time = ADDCOLUMNS('RawData',"@time",
VAR _H = HOUR('RawData'[TimeOnCourt]) *60*60
VAR _M = MINUTE('RawData'[TimeOnCourt])*60
VAR _S = SECOND('RawData'[TimeOnCourt])
RETURN
_H+_M+_S)
VAR _Result = DIVIDE(SUMX(_time,[@time]),_Count)
RETURN
VAR Hours =
INT (_Result/ 3600)
VAR Minutes = INT ( MOD( _Result - ( Hours * 3600 ),3600 ) / 60)
VAR Seconds =
ROUNDUP(MOD ( MOD( _Result - ( Hours * 3600 ),3600 ), 60 ),0)
VAR H =
IF ( LEN ( Hours ) = 1,
CONCATENATE ( "0", Hours ),
CONCATENATE ( "", Hours )
)
VAR M =
IF (
LEN ( Minutes ) = 1,
CONCATENATE ( "0", Minutes ),
CONCATENATE ( "", Minutes )
)
VAR S =
IF (
LEN ( Seconds ) = 1,
CONCATENATE ( "0", Seconds ),
CONCATENATE ( "", Seconds )
)
RETURN
IF(_Result,
TIME(H,M,S))
------------ or ------------
Average TimeOnCourt3 =
VAR _Count = COUNTROWS('RawData')
VAR _time = ADDCOLUMNS('RawData',"@time",
VAR _H = HOUR('RawData'[TimeOnCourt]) *60*60
VAR _M = MINUTE('RawData'[TimeOnCourt])*60
VAR _S = SECOND('RawData'[TimeOnCourt])
RETURN
_H+_M+_S)
VAR _Result = DIVIDE(SUMX(_time,[@time]),_Count)
RETURN
VAR Hours =
INT (_Result/ 3600)
VAR Minutes = INT ( MOD( _Result - ( Hours * 3600 ),3600 ) / 60)
VAR Seconds =
ROUNDUP(MOD ( MOD( _Result - ( Hours * 3600 ),3600 ), 60 ),0)
RETURN
IF(_Result,
TIME(Hours,Minutes,Seconds))