Adding Your Own Calculations to a Pivot Table

If Projector does not provide a calculation in a pivot table, but provides the raw data from which you could derive the calculation, then you can do it yourself. When combined with Excel and Access Refresh Files you can have a heavily customized spreadsheet tailored to your organizations needs.

In this screenshot I have added a custom data field that calculates the variance % from the data in the person hours booked and person hours reported rows.

Adding a New Data Field

1. Click in the pivot table. Choose Options from the new section that appears in the toolbar ribbon.
   
2. Click Fields, Items & Sets and choose Calculated Field...
   
3. Enter a new field name and a formula for calculating it. You may double click existing fields from the Fields list to insert them into the formula. 
   
4. In this example we will find the difference between System Revenue and System Revenue Recognized to calculate Unrecognized System Revenue.
   
5. Click Add once you are done setting up the field. You can add more fields or click OK if you are done.
6. The pivot table now includes the new field.
   
7. If you want to rename the field or change how it is calculated double click on it in your pivot table.
   

Modify an Existing Field Calculation

You can modify your new field calculations or edit a Projector field calculation by returning to the Fields button.

From here, instead of typing in a new name, instead use the dropdown list to select the formula you wish to edit.