Aggregating Duration/Time

[Anonymous]

How can I aggregate the total hours for each ticket priority? I tried to convert the time to decimal but when I tried to get the average, its not aggregating. please help

[v-huizhn-msft]

Hi @Anonymous,

The total of 'average of Total Time' and 'Average of Activity Hours'(highlighted in yellow backgroud) should be 1076488.39 and 7.01, right? 'average of Total Time' and 'Average of Activity Hours' are calculted in a measure or calculated column? The total results depend on your formula. Could you please share more details and your sample table for further analysis?

Best Regards,
Angelia

[Anonymous]

hi @v-huizhn-msft thank you so much for responding. Actually, 'average of Total Time' and 'Average of Activity Hours' are two different columns. What I am concern is converting the Average of Total Time to HH:MM:SS, because currently its in seconds(decimal) I was able to convert it on the query but it doesn't aggregate. 

the Average of Total Time is being calculated and converted on the query itself. 

---          SEC_TO_TIME(AVG(TIME_TO_SEC(total_time)))   AS  Average of Total Time        ----

Please dont mind the activity hours because its for different column.

I want to get the average time for each priority. 

Please advise. Thank you

[v-huizhn-msft]

Hi @Anonymous,

Please create the measure using the following formula.

Measure-format = FORMAT(AVERAGE(Table2[total_time]),"HH:MM:SS")

Best Regards,
Angelia

[Anonymous]

Hi @v-huizhn-msft Angelia, 

Thank you for your response but how can I get the average by grouping them by priority?

Thanks!