Excel Pivot Table Calculated Fields

[AlexaS]

Hello,

I've looked for this before, but it's come up again at my job and now I need to find a definitive answer (hopefully in the affirmative).

I have data that is linked to a pivot table and on the side of the pivot table are calculated fields (we call them "bolt-on fields"). We want to try to add these fields directly into the pivot table.

EXAMPLES
Data Table:

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Fiscal Year End[/TD]
[TD]November Sales[/TD]
[TD]November Cost[/TD]
[/TR]
[TR]
[TD]2014[/TD]
[TD]5[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]2014[/TD]
[TD]5[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]2014[/TD]
[TD]5[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]2014[/TD]
[TD]5[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]2014[/TD]
[TD]5[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]2015[/TD]
[TD]6[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]2015[/TD]
[TD]6[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]2015[/TD]
[TD]6[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]2015[/TD]
[TD]6[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]2015[/TD]
[TD]6[/TD]
[TD]3[/TD]
[/TR]
</tbody>[/TABLE]

Pivot Table:
D & E are manually typed in formulas to the right of the pivot table currently

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD][/TD]
[TD]Fiscal[/TD]
[TD]Year[/TD]
[TD]FY15 v. FY14[/TD]
[TD]GM% Diff
x Net Sales[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Data[/TD]
[TD]2014[/TD]
[TD]2015[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Sumof Net Sales November[/TD]
[TD]25[/TD]
[TD]30[/TD]
[TD]= C3 - B3[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]Sumof Cost November[/TD]
[TD]10[/TD]
[TD]15[/TD]
[TD]= C4 - B4[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]Gross Margin %[/TD]
[TD][/TD]
[TD][/TD]
[TD]= (C3 - C4) / C3[/TD]
[TD]= C3 * D5[/TD]
[/TR]
</tbody>[/TABLE]


The problem is the way that the data is set up. I can't figure out how to do the calculations in D & E and actually get results. There is nothing in the data table column "November Sales" or "November Cost" that tell it which year it's in. Is there a way with a calculated field that I can say:

= 'November Sales' in 'Fiscal Year' 2015 - 'November Sales' in 'Fiscal Year' 2014

Even if I first have to set up a field for "November Sales in Fiscal Year 2015", is there a way to do this? I'm banging my head against a brick wall trying to find it. And the whole reason is because we want it to be "pivot-able". I dislike pivot tables severely.

Any help would be greatly appreciated.

 

[Pup Denab]

HI AlexaS
In this situation, I would recommend doing the calculations in the data set, not in the pivot table, then you can simply view the results in the selected format required, as a pivot table, you will most likely use sumif or sumifs, as that is what the pivot table is doing, also it uses countif & countifs, does that help, or I can try to do it for you if required

 

[AlexaS]

Thank you for your response. That is the other avenue I'm attempting. Although it is posing it's own problems, as the data set is set up as a table. So my problem with that is trying to make the formula (which pulls a table heading) dynamic so that I don't have to change it every month.

 

[RoryA]

You can do D3 and D4 in the pivot using calculated items, but not the others, I'm afraid.