Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

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

Posted on 2014-07-24
2
Medium Priority
?
526 Views
Last Modified: 2014-07-24
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
0
Comment
Question by:Jeff Geiselman
2 Comments
 
LVL 93

Accepted Solution

by:
Patrick Matthews earned 1000 total points
ID: 40218087
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
 
LVL 1

Author Closing Comment

by:Jeff Geiselman
ID: 40218166
Thanks, that will do quite nicely.
I was trying 'Calculated Fields' instead of  'Calculated Items'.
0

Featured Post

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
Microsoft's Excel has many features that most people will never need nor take advantage of.  Conditional formatting is one feature that you may find a necessity once you start using it.
Viewers will learn how to maximize accessibility options in an Excel workbook for users with accessibility issues.
Viewers will learn various types of data validation for different data types in Excel 2013.

772 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question