Calculated Field/ Item query - calculating differences

[fwd105]

Hi I’m currently trying to work out the difference of two valuations (internal and external) in a pivot I have created.
Currently my Pivot is arranged as follows, source data (internal or external) as column labels, Product as row labels with a further break down by office region of transaction.
I would like to add an additional column which calculates the difference between external and internal valuations using Calculated Field or Item but not sure how to go about writing the formula. I’ve tried a few formulas but so far I have yet to find the desired result. Below is what my pivot currently looks like.

Sum of Value	Column Labels
Row Labels	External	Internal	Grand Total
Product A	-10218097.27	-9443842.67	-19661939.94
London	-3810892	-3401781.21	-7212673.21
New York	-1111252	-811930.89	-1923182.89
Sydney	-1121444.948	-1105834.76	-2227279.708
Tokyo	-4174508.318	-4124295.81	-8298804.128
Product B	-1387734.269	-1311762.76	-2699497.029
London	-729094	-681590.82	-1410684.82
Sydney	-658640.2692	-630171.94	-1288812.209
Grand Total	-11605831.53	-10755605.43	-22361436.96

<tbody>
</tbody><colgroup><col><col><col span="2"></colgroup>

 

[RoryA]

It looks as though it would be a calculated item using:
='External'-'Internal'
or vice versa.

 

[fwd105]

When I do that my pivot creates additional columns

	Column Labels
	External		Internal		Total Sum of Value	Total Sum of Field1
Row Labels	Sum of Value	Sum of Field1	Sum of Value	Sum of Field1
Product A	-10218097.27	#VALUE!	-9443842.67	#VALUE!	-19661939.94	#VALUE!
London	-3810892	#VALUE!	-3401781.21	#VALUE!	-7212673.21	#VALUE!
New York	-1111252	#VALUE!	-811930.89	#VALUE!	-1923182.89	#VALUE!
Sydney	-1121444.948	#VALUE!	-1105834.76	#VALUE!	-2227279.708	#VALUE!
Tokyo	-4174508.318	#VALUE!	-4124295.81	#VALUE!	-8298804.128	#VALUE!
Product B	-1387734.269	#VALUE!	-1311762.76	#VALUE!	-2699497.029	#VALUE!
London	-729094	#VALUE!	-681590.82	#VALUE!	-1410684.82	#VALUE!
Sydney	-658640.2692	#VALUE!	-630171.94	#VALUE!	-1288812.209	#VALUE!
Grand Total	-11605831.53	#VALUE!	-10755605.43	#VALUE!	-22361436.96	#VALUE!

<tbody>
</tbody><colgroup><col><col><col><col><col><col><col></colgroup>

 

[RoryA]

I think you created a calculated Field, not Item.

 

[fwd105]

I think you created a calculated Field, not Item.

Thank you. After a couple more tries that now works. Calculated item was not previously showing up due to being in an incorrect field. Now to figure out how to get rid of the empty rows.