Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
9 years ago

Aggregating Duration/Time

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  
Need Help
Tips and Tricks
Tutorial Requests
Anonymous's avatar
Anonymous
Not applicable
9 years ago

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's avatar
v-huizhn-msft
Microsoft Employee
9 years ago

Hi Anonymous,

Please create the measure using the following formula.

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





Best Regards,
Angelia

  • Anonymous's avatar
    Anonymous
    Not applicable
    9 years ago

    Hi v-huizhn-msft Angelia, 

     

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

     

    Thanks!