Pivot Table Calculated Fields

[Simon]

Hi I have a pivot table with one data field which is "Revenue". On the pivot
table I've split the revenue by the Ledger field which holds the values of
"Actual" or "Budget". I want to be able to add a third field in which
effectively is Actual - Budget, is this possible? If so how would I do this?

Is it possible to change the Grand Total Field from Grand Totalling but to
do a subtraction of the Actual - Budget?

 

[Shane Devenshire]

Hi,

If you data looks like this:

Ledger Amount
Actual 6
Budget 53
Actual 76
Budget 14
Actual 86
Budget 10
Actual 23
Budget 9
Actual 40
Budget 77
Actual 57
Budget 20
Actual 11
Budget 8

You can do this by creating a Calculated Item or using Difference From:

1. Drag the data field into the data area a second time.
2. Right-click this new field and choose Field Setting, Options and pick
Difference From in the Show data as area.
3. Pick Ledger and choose Budget click OK.
4. Drag the data field button to the Column area.
5. Turn of the grand total for Rows.
You may have to rearrange the Actual and Budget to make things look good,
you can do this by dragging.

To use the Calculated Item approach
1. Select the Budget or Actual field title in the pivot chart and choose
PivotTable, Formulas, Calculated item
2. Enter a name for your calculation like Variance
3. Select the Formula box and remove the 0 and pick Ledger from the Fields
and then Double-click Budget type - and double-click Actuals and click OK.
4. Turn off the grand total for rows.