Pivot calculation - variance between count of 2 fields

[davemcwish]

I'm needing to create a pivot table that dynamically generates a variance between two data fields containing attribute data. I've put a mockup on Dropbox to show the source data (after denormalizing) and the output table I'm trying to produce. I know that I can perform a subtraction either using Cell references or GetPivotData but neither of these will work if the pivot structure changes so I'm looking to embed a calculation within a pivot. I should add that there are additional fields that will be used for analysis but I cut them out from the mockup as they aren't relevant for the calculation.

Thoughts ?

 

[Andrew Poulsom]

That would require a Calculated Item, which isn't possible with your data layout. It would be possible with this layout (Count of item in the Data area):

Excel 2010
	A	B	C	D
1	Item	Criteria	State	Level
2	#1	Criteria A	After	Level A
3	#1	Criteria A	Before	Level A
4	#1	Criteria B	After	Level A
5	#1	Criteria B	Before	Level A
6	#1	Criteria C	After	Level D
7	#1	Criteria C	Before	Level D
8	#1	Criteria D	After	Level A
9	#1	Criteria D	Before	Level A
10	#1	Criteria E	After	Level B
11	#1	Criteria E	Before	Level A
12	#1	Criteria F	After	Level A
13	#1	Criteria F	Before	Level A
14	#1	Criteria G	After	Level B
15	#1	Criteria G	Before	Level A
16	#1	Criteria H	After	Level C
17	#1	Criteria H	Before	Level B
18	#1	Criteria I	After	Level C
19	#1	Criteria I	Before	Level B
20	#1	Criteria J	After	Level B
21	#1	Criteria J	Before	Level A
22	#1	Criteria K	After	Level B
23	#1	Criteria K	Before	Level A
24	#1	Criteria L	After	Level C
25	#1	Criteria L	Before	Level B
26	#1	Criteria M	After	Level A
27	#1	Criteria M	Before	Level A
28	#1	Criteria N	After	Level B
29	#1	Criteria N	Before	Level A
30	#1	Criteria O	After	Level B
31	#1	Criteria O	Before	Level B
32	#1	Criteria P	After	Level B
33	#1	Criteria P	Before	Level A
34	#1	Criteria Q	After	Level A
35	#1	Criteria Q	Before	Level A
36	#1	Criteria R	After	Level D
37	#1	Criteria R	Before	Level D
38	#1	Criteria S	After	Level C
39	#1	Criteria S	Before	Level B
40	#1	Criteria T	After	Level D
41	#1	Criteria T	Before	Level D
42	#1	Criteria U	After	Level B
43	#1	Criteria U	Before	Level B
44	#1	Criteria V	After	Level C
45	#1	Criteria V	Before	Level B
46	#1	Criteria W	After	Level D
47	#1	Criteria W	Before	Level D
Sheet2

 

[davemcwish]

Ok Andrew, I understand the reasoning behind your suggested layout but you've completely lost me with 'Calculated Item'. If I setup the table with Row = Criteria, Column = Level and Data = Count of State I can't see how to create a calculated item that I will put in the Values area that is = [Count of] State(After) - [Count of] State(Before).

 

[Andrew Poulsom]

I added a calculated Item with the formula =After-Before:

Excel 2010
	F	G	H	I	J	K
1	Count of Item	Column Labels
2	Row Labels	Level A	Level B	Level C	Level D	Grand Total
3	Criteria A	2	0	0	0	2
4	After	1				1
5	Before	1				1
6	Formula1	0	0	0	0	0
7	Criteria B	2	0	0	0	2
8	After	1				1
9	Before	1				1
10	Formula1	0	0	0	0	0
11	Criteria C	0	0	0	2	2
12	After				1	1
13	Before				1	1
14	Formula1	0	0	0	0	0
15	Criteria D	2	0	0	0	2
16	After	1				1
17	Before	1				1
18	Formula1	0	0	0	0	0
19	Criteria E	0	2	0	0	2
20	After		1			1
21	Before	1				1
22	Formula1	-1	1	0	0	0
23	Criteria F	2	0	0	0	2
Sheet1