Excel Pivot Table Manipulations

Question

I work for a Helpdesk and I am trying to create a pivot table that shows the resolution rate of our analysts. This is the pivot table I currently have shows each analysts name on the left and the data in the table is the number of tickets that we created. On the top of the pivot table, the only field I have is split into two columns, Dispatched and Resolved.

What I am trying to do is add another field that basically shows their resolution rate or Resolved/Grand Total. If someone could point me in the right direction it would be greatly appreciated.

Answer 1

If I understand you correctly, your pivot table has:

• Name as a row label
• The state of the ticket (Dispatched or Resolved) as a column label
• The Count of the ticket number as the Value

Then you can:

1. Change the field settings of the Count of ticket number to Show values as % of row. That will change the totals to percentages for the data; or
2. If you want to show both counts and percentages, then you'd need to add Count of the ticket number as a Value a second time; you can then set one as just the count and the second as a percentage. This looks a little messy but you can play around with the formatting (showing/hiding totals, etc.) to make it a bit nicer
3. If you just want a %age at the end (Dispatched/Resolved or something like that) it may be simpler just to add a formula to the right of your pivot table and calculate it outside of the pivot table. You'll end up with something like =GETPIVOTDATA("Count of Ticket No",$F$2,"Name",F5,"State","Dispatched")/(GETPIVOTDATA("Count of Ticket No",$F$2,"Name",F5,"State","Dispatched")+GETPIVOTDATA("Count of Ticket No",$F$2,"Name",F5,"State","Resolved")), which is really just pivot table code for =Dispatched/(Resolved+Dispatched). When you add a cell in a pivot table into a formula, it gets entered as GETPIVOTDATA("Count of Ticket No",$F$2,"Name","bob","State","Dispatched"), which means 'Get the value of "Count of Ticket No" column from the pivot table at $F$2 where Name=Bob and State = Dispatched'. I often change these to replace the hard coded bob and Dispatched values with the cell references, so that I can drag the formulas and also so they update if the pivot table resizes. e.g. GETPIVOTDATA("Count of Ticket No",$F$2,"Name",F6,"State",G$3)

Answer 2

You could add an auto calculating field to the pivot which would do that in the actual pivot table. On iPhone at moment but wrote an example as answer to another question here: How do I show the average by month in a pivot table?

This has the advantage of not getting overwritten or missing data when the pivot gets resized (eg when new staff are added etc.)