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.


[TABLE="width: 384"]
<tbody>[TR]
[TD]Sum of Value[/TD]
[TD]Column Labels[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Row Labels[/TD]
[TD]External[/TD]
[TD]Internal[/TD]
[TD]Grand Total[/TD]
[/TR]
[TR]
[TD]Product A[/TD]
[TD="align: right"]-10218097.27[/TD]
[TD="align: right"]-9443842.67[/TD]
[TD="align: right"]-19661939.94[/TD]
[/TR]
[TR]
[TD]London[/TD]
[TD="align: right"]-3810892[/TD]
[TD="align: right"]-3401781.21[/TD]
[TD="align: right"]-7212673.21[/TD]
[/TR]
[TR]
[TD]New York[/TD]
[TD="align: right"]-1111252[/TD]
[TD="align: right"]-811930.89[/TD]
[TD="align: right"]-1923182.89[/TD]
[/TR]
[TR]
[TD]Sydney[/TD]
[TD="align: right"]-1121444.948[/TD]
[TD="align: right"]-1105834.76[/TD]
[TD="align: right"]-2227279.708[/TD]
[/TR]
[TR]
[TD]Tokyo[/TD]
[TD="align: right"]-4174508.318[/TD]
[TD="align: right"]-4124295.81[/TD]
[TD="align: right"]-8298804.128[/TD]
[/TR]
[TR]
[TD]Product B[/TD]
[TD="align: right"]-1387734.269[/TD]
[TD="align: right"]-1311762.76[/TD]
[TD="align: right"]-2699497.029[/TD]
[/TR]
[TR]
[TD]London[/TD]
[TD="align: right"]-729094[/TD]
[TD="align: right"]-681590.82[/TD]
[TD="align: right"]-1410684.82[/TD]
[/TR]
[TR]
[TD]Sydney[/TD]
[TD="align: right"]-658640.2692[/TD]
[TD="align: right"]-630171.94[/TD]
[TD="align: right"]-1288812.209[/TD]
[/TR]
[TR]
[TD]Grand Total[/TD]
[TD="align: right"]-11605831.53[/TD]
[TD="align: right"]-10755605.43[/TD]
[TD="align: right"]-22361436.96[/TD]
[/TR]
</tbody><colgroup><col><col><col span="2"></colgroup>[/TABLE]

 

[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 [TABLE="width: 731"]
<tbody>[TR]
[TD][/TD]
[TD]Column Labels[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]External[/TD]
[TD][/TD]
[TD]Internal[/TD]
[TD][/TD]
[TD]Total Sum of Value[/TD]
[TD]Total Sum of Field1[/TD]
[/TR]
[TR]
[TD]Row Labels[/TD]
[TD]Sum of Value[/TD]
[TD]Sum of Field1[/TD]
[TD]Sum of Value[/TD]
[TD]Sum of Field1[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Product A[/TD]
[TD="align: right"]-10218097.27[/TD]
[TD="align: center"]#VALUE![/TD]
[TD="align: right"]-9443842.67[/TD]
[TD="align: center"]#VALUE![/TD]
[TD="align: right"]-19661939.94[/TD]
[TD="align: center"]#VALUE![/TD]
[/TR]
[TR]
[TD]London[/TD]
[TD="align: right"]-3810892[/TD]
[TD="align: center"]#VALUE![/TD]
[TD="align: right"]-3401781.21[/TD]
[TD="align: center"]#VALUE![/TD]
[TD="align: right"]-7212673.21[/TD]
[TD="align: center"]#VALUE![/TD]
[/TR]
[TR]
[TD]New York[/TD]
[TD="align: right"]-1111252[/TD]
[TD="align: center"]#VALUE![/TD]
[TD="align: right"]-811930.89[/TD]
[TD="align: center"]#VALUE![/TD]
[TD="align: right"]-1923182.89[/TD]
[TD="align: center"]#VALUE![/TD]
[/TR]
[TR]
[TD]Sydney[/TD]
[TD="align: right"]-1121444.948[/TD]
[TD="align: center"]#VALUE![/TD]
[TD="align: right"]-1105834.76[/TD]
[TD="align: center"]#VALUE![/TD]
[TD="align: right"]-2227279.708[/TD]
[TD="align: center"]#VALUE![/TD]
[/TR]
[TR]
[TD]Tokyo[/TD]
[TD="align: right"]-4174508.318[/TD]
[TD="align: center"]#VALUE![/TD]
[TD="align: right"]-4124295.81[/TD]
[TD="align: center"]#VALUE![/TD]
[TD="align: right"]-8298804.128[/TD]
[TD="align: center"]#VALUE![/TD]
[/TR]
[TR]
[TD]Product B[/TD]
[TD="align: right"]-1387734.269[/TD]
[TD="align: center"]#VALUE![/TD]
[TD="align: right"]-1311762.76[/TD]
[TD="align: center"]#VALUE![/TD]
[TD="align: right"]-2699497.029[/TD]
[TD="align: center"]#VALUE![/TD]
[/TR]
[TR]
[TD]London[/TD]
[TD="align: right"]-729094[/TD]
[TD="align: center"]#VALUE![/TD]
[TD="align: right"]-681590.82[/TD]
[TD="align: center"]#VALUE![/TD]
[TD="align: right"]-1410684.82[/TD]
[TD="align: center"]#VALUE![/TD]
[/TR]
[TR]
[TD]Sydney[/TD]
[TD="align: right"]-658640.2692[/TD]
[TD="align: center"]#VALUE![/TD]
[TD="align: right"]-630171.94[/TD]
[TD="align: center"]#VALUE![/TD]
[TD="align: right"]-1288812.209[/TD]
[TD="align: center"]#VALUE![/TD]
[/TR]
[TR]
[TD]Grand Total[/TD]
[TD="align: right"]-11605831.53[/TD]
[TD="align: center"]#VALUE![/TD]
[TD="align: right"]-10755605.43[/TD]
[TD="align: center"]#VALUE![/TD]
[TD="align: right"]-22361436.96[/TD]
[TD="align: center"]#VALUE![/TD]
[/TR]
</tbody><colgroup><col><col><col><col><col><col><col></colgroup>[/TABLE]

 

[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.