Forum Discussion
Solved
help with pivot table
hello guys,
i am new to pivot tables so please excuse me if this is easy for you.
i have an excel sheet that contains IT tickets with their resolve time and response time. which from i created a pivot table for the average resolution time and the percentage of tickets that were resolved under 4 hours and the graph looks like this
is there a way to show the number of tickets that took less than 1 hours to resolve , more than 4 hours to resolve and tickets that took between 1 and 4 hours to resolve ? how to put those in a graph in buckets or stacked columns ( not sure what the terminology for this is )
thank you for your help
That's with PivotTable based on data model if your Excel supports it. Creating PivotTable check "Add data to data model", create measures you need, like for such sample
as
Average Total:=AVERAGE(Table1[Time]) Average < 1 Hr:=CALCULATE( AVERAGE(Table1[Time]), Table1[Time] < 1/24 )create chart based on them and add cosmetic.
- Easy by adding a formula next to the time which expresses these three periods. Assuming spent time is in B2:
=IF(B2<TIMEVALUE("1:00:00"),"<1 hr",IF(B2<TIMEVALUE("4:00:00"),"1 - 4 hrs",">4 hrs")) That's with PivotTable based on data model if your Excel supports it. Creating PivotTable check "Add data to data model", create measures you need, like for such sample
as
Average Total:=AVERAGE(Table1[Time]) Average < 1 Hr:=CALCULATE( AVERAGE(Table1[Time]), Table1[Time] < 1/24 )create chart based on them and add cosmetic.
- thank you for this , it's wonderful , i was able to do it on my excel , however ,i'd like to show the NUMBER of tickets resolved under 1 hour and the NUMBER of tickets resolved over 4 hours
