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:

Fiscal Year End	November Sales	November Cost
2014	5	2
2014	5	2
2014	5	2
2014	5	2
2014	5	2
2015	6	3
2015	6	3
2015	6	3
2015	6	3
2015	6	3

<tbody>
</tbody>

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

	A	B	C	D	E
1		Fiscal	Year	FY15 v. FY14	GM% Diff x Net Sales
2	Data	2014	2015
3	Sumof Net Sales November	25	30	= C3 - B3
4	Sumof Cost November	10	15	= C4 - B4
5	Gross Margin %			= (C3 - C4) / C3	= C3 * D5

<tbody>
</tbody>

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.