Excel 2010 PivotTable- help creating a complex 'Calculated Field'

I am trying to compare similar records from two different sources.
I would like to identify those 'Item's where the Qtys are different.

The data sheet looks like the following:
Source      Item        Qty   (see attached file also)
File A         X             10
File B         X             10
File A         Y               2
File B         Y               3
File A         Z               8
File B         Z               4

I created a PivotTable with the following fields:
Row Labels = 'Item'
Column Labels = 'Source'
Values = 'Qty' (sum)

The data records for the 'Column Label' called 'Source' contains only 'File A' or 'File B'.

Is there a way to create a 'Calculated Field' within the PivotTable that can display the difference between the 'Qty' of the two Sources?
C--Users-jgeiselman-Desktop-PivotTable-E
LVL 1
Jeff GeiselmanAsked:
Who is Participating?
 
Patrick MatthewsConnect With a Mentor Commented:
To do it all as a PivotTable does not work, but you can get close to it be creating Calculated Items for your Source field.

1) Select the Source field in the PivotTable
2) On the Ribbon, select PivotTable Tools/Options/Calculations/Fields Items and Sets, and pick Calculated Item
3) Create calculated items for Difference (Field B - Field A), and Grand Total (Field A + Field B)
4) Turn off column grand totals for the PivotTable
0
 
Jeff GeiselmanAuthor Commented:
Thanks, that will do quite nicely.
I was trying 'Calculated Fields' instead of  'Calculated Items'.
0
All Courses

From novice to tech pro — start learning today.