Forum Discussion
Average Duration
My 'Fact Table'(Duration) ranges from 00:00:00 to 23:59:59
How do I calculate the Average Duration per 'Fact Table'(CP ID)
Please assist,
Many thanks
Hi, Anonymous
Power BI does not support duration type. Please try follow steps:
1.Change the data type of duration column to HH:MM:SS and add a calculated column to convert the durtaion to time in secondsTotal_seconds = SECOND('Table'[Duration])+MINUTE('Table'[Duration])*60+HOUR('Table'[Duration])*36002. Create a measure to calculate the average second and reconvert it to duration
Average = VAR _avg=AVERAGE('Table'[Total_seconds]) VAR HoursPart = MOD ( INT ( _avg / 3600 ), 24 ) VAR MinPart = MOD ( INT ( _avg / 60 ), 60 ) VAR SecPart = MOD ( INT ( _avg ), 60 ) RETURN FORMAT ( CONVERT ( HoursPart & ":" & MinPart & ":" & SecPart, DATETIME ), "hh:mm:ss" )Please check my attached sample file for more details.
Best Regards,
Community Support Team _ Eason
2 Replies
Try
Average duration per CP ID = CALCULATE( AVERAGE('Fact Table'[Duration]), ALLEXCEPT('Fact Table'[CP ID]))Hi, Anonymous
Power BI does not support duration type. Please try follow steps:
1.Change the data type of duration column to HH:MM:SS and add a calculated column to convert the durtaion to time in secondsTotal_seconds = SECOND('Table'[Duration])+MINUTE('Table'[Duration])*60+HOUR('Table'[Duration])*36002. Create a measure to calculate the average second and reconvert it to duration
Average = VAR _avg=AVERAGE('Table'[Total_seconds]) VAR HoursPart = MOD ( INT ( _avg / 3600 ), 24 ) VAR MinPart = MOD ( INT ( _avg / 60 ), 60 ) VAR SecPart = MOD ( INT ( _avg ), 60 ) RETURN FORMAT ( CONVERT ( HoursPart & ":" & MinPart & ":" & SecPart, DATETIME ), "hh:mm:ss" )Please check my attached sample file for more details.
Best Regards,
Community Support Team _ Eason
