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

4 Replies

  • v-huizhn-msft's avatar
    v-huizhn-msft
    Microsoft Employee
    9 years ago

    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'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