Excel Pivot Table Calculated Fields

AlexaS

Board Regular
Joined
Oct 12, 2012
Messages
155
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 EndNovember SalesNovember Cost
201452
201452
201452
201452
201452
201563
201563
201563
201563
201563

<tbody>
</tbody>

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

ABCDE
1FiscalYearFY15 v. FY14GM% Diff
x Net Sales
2Data20142015
3Sumof Net Sales November2530= C3 - B3
4Sumof Cost November1015= C4 - B4
5Gross 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.
 

Excel Facts

Can you AutoAverage in Excel?
There is a drop-down next to the AutoSum symbol. Open the drop-down to choose AVERAGE, COUNT, MAX, or MIN

Pup Denab

Active Member
Joined
May 12, 2013
Messages
299
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

Board Regular
Joined
Oct 12, 2012
Messages
155
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

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
36,696
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
You can do D3 and D4 in the pivot using calculated items, but not the others, I'm afraid.
 

Watch MrExcel Video

Forum statistics

Threads
1,129,472
Messages
5,636,516
Members
416,920
Latest member
Riskyplan

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top