Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.charting
|
|||
|
|||
Pivot Table Calculated Fields
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? |
#2
Posted to microsoft.public.excel.charting
|
|||
|
|||
Pivot Table Calculated Fields
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. -- If this helps, please click the Yes button Cheers, Shane Devenshire "Simon" wrote: 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? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
pivot table calculated fields | Excel Discussion (Misc queries) | |||
Pivot Table Calculated Fields | Excel Discussion (Misc queries) | |||
Pivot table and calculated fields | Excel Discussion (Misc queries) | |||
Pivot Table with Calculated Fields | Excel Discussion (Misc queries) | |||
Sum of Calculated fields in Pivot Table | Excel Discussion (Misc queries) |