Calculated field in PivotTables

[Hawjeen]

Hi,

First time posting but been following for a while.

I have a problem i simply cant figure out how to fix.

In my pivot table, i have showed sales as % of grand total, for current year and previuos year, divided into categories and i want to show the difference through a calculated field.
but here comes my problem, i simply dont know how to refer to it the correct way, so it shows the delta between CY and LY.

usually it is enough to write "='CY'-'LY'" in calculated field, but that shows the differnce in the initial value, and not the differnce between the "% of grand total".

hope it is understandable and someone can hel.

regards
Hawjeen

 

[oldbrewer]

[TABLE="width: 448"]
<colgroup><col width="64" span="7" style="width:48pt"> </colgroup><tbody>[TR]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD="align: right"]2016[/TD]
[TD][/TD]
[TD="align: right"]2017[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]computers[/TD]
[TD="align: right"]2050[/TD]
[TD="class: xl24, align: right"]44.52[/TD]
[TD="align: right"]2300[/TD]
[TD="class: xl24, align: right"]49.95[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]printers[/TD]
[TD="align: right"]1405[/TD]
[TD="class: xl24, align: right"]30.51[/TD]
[TD="align: right"]1600[/TD]
[TD="class: xl24, align: right"]34.74[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]routers[/TD]
[TD="align: right"]750[/TD]
[TD="class: xl24, align: right"]16.29[/TD]
[TD="align: right"]900[/TD]
[TD="class: xl24, align: right"]19.54[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]keyboards[/TD]
[TD="align: right"]400[/TD]
[TD="class: xl24, align: right"]8.69[/TD]
[TD="align: right"]500[/TD]
[TD="class: xl24, align: right"]10.86[/TD]
[TD="class: xl24"][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]total[/TD]
[TD="align: right"]4605[/TD]
[TD][/TD]
[TD="align: right"]5300[/TD]
[TD][/TD]
[TD="class: xl24"][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="class: xl24"][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="class: xl24"][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="class: xl24"][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="class: xl24"][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="class: xl24"][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="class: xl24"][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="class: xl24"][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="class: xl24"][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD="colspan: 2"]what is your question[/TD]
[TD][/TD]
[TD][/TD]
[TD="class: xl24"][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD="colspan: 3"]in terms of this layout ?[/TD]
[TD][/TD]
[TD="class: xl24"][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[Hawjeen]

i want the equation 44.52-49.95 in a calculated field. i know it is pretty easy to do outside of the pivot, but i need it inside of the PT.

just for clarification i dont have the sales in my PT, eg. "2050". my "44.52" is shown through the setting for show value as % of grand total.

regards
Hawjeen