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:
<tbody>
</tbody>
Pivot Table:
D & E are manually typed in formulas to the right of the pivot table currently
<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.
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.