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.


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)
| improve this answer | |
  • I think the problem lies in the fact that the orginal table has HD Resolved as the Field name and the options in this field are wither Resolved or Dispatched. I cannot seem to say just display a count of the tickets that are dispatched or that are resolved since they are included in the HD resoloved field. – fastrack20 May 31 '11 at 13:42

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.)

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.