Pivot table calculation field

[BTTFin]

Hello -- I hope someone can help me with a Pivot Table Calculation Field problem I worked on it, for hours today.
Table has 3 columns

1. Dept# (text)
2. Type of amounts (text) 5 Types = Budget, Grant, Infrastructure, Projects, Technology
3. Amounts (currency)

Pivot table

- Rows = Dept#
- Columns = Type
- Values = Amounts

There are 5 columns of amounts. I want to calculate (Budget + Grant) -(Infrastructure + Projects + Technology) Can this be done in the Calculation field?
Attached is a worksheet illustrating this quandary.

Thank you for thinking about this problem
BTTFin

[JeteMc]

Hello BTTFin and Welcome to Excel Forum.
1. Select cell F14
2. Select Fields, Items & Sets from the Analyze sub tab of PivotTable Tools tab
3. Select Calculated Item
4. Put the following in the Formula window: =Budget +Grant -(Infrastucture +Projects +Technology )
5. Select OK
Let us know if you have any questions.

[BTTFin]

Thank you JeteMc It works. Once I employed Calculation items vs Calculated fields, I was able to calculate a variance. Nonetheless as you guessed this tiny worksheet was constructed for demonstration purposes. Indeed, there was an additional "layer" nested into the department. When this method was employed to the added column the whole sheet expanded and crashed excel.. I must find another method.

[JeteMc]

One option might be to use power query.
The following could be used as the advanced editor code:

Please Login or Register  to view this content.

Let us know if you have any questions.