cancel
Showing results 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

Helper III

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

Tested measure (not working):

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

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

6 REPLIES 6
Super User

Based on your description, I created data to reproduce your scenario. The pbix file is attached in the end.
https://1drv.ms/u/s!AiUZ0Ws7G26Rh3ONItvQtPlETpMW?e=dW9liP

Helper III

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.

Super User

``````Average TimeOnCourt2 =
VAR _Count = COUNTROWS('RawData')
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 _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))``````

Super User

you can create an example where the measure counts incorrectly?

Super User

@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))

Helper III

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

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.