Calculated Field

[WGarrison]

Hello Everyone. I am trying to analyze Year over Year Sales in a Pivot Table. I have struck out with conditional formatting so now I am playing around with Calculated Fields. One would think this was easy....I have proven it is not.

I want to simply subtract 2024 v 2023. I should either have a positive or negative number indicating growth or loss. When I try to use Calculated Fields, I cannot find the fields that would populate the values I need to subtract (my annual sales results). I am doing something wrong and could use some assistance, please.

I have attached not only a snip of my pivot table but also the way I have the fields laid out. I am going to assume, it is my PivotTable Fields not set up properly to allow me to do this calculation.

Thank you,

 

[Cubist]

The year 2023 and Year 2024 are not fields, but categories of a field i.e. "year". To show the difference between 2023 and 2024 you'd have to duplicate the table
and right-click -> summarize values as -> more options -> show data as -> difference from -> base field = year -> base item = previous.
Note that this would effect the whole table not just the grand total.

 

[WGarrison]

The year 2023 and Year 2024 are not fields, but categories of a field i.e. "year". To show the difference between 2023 and 2024 you'd have to duplicate the table
and right-click -> summarize values as -> more options -> show data as -> difference from -> base field = year -> base item = previous.
Note that this would effect the whole table not just the grand total.

That worked - is there a way to keep the integrity of the 2023 and 2024 results and show that calculation in a 3rd column?

 

[Cubist]

To my knowledge, there isn't. You can hide the extra columns in between and re-format a bit to make it look like 1 table.

 

[Alex Blakenburg]

As @Cubist pointed out because your year is in a column (which is a good thing), they are not separate fields the are "items" in a field.
If you click on any cell in year column or the heading for that column, under Field, Items & Set there should be a Calculated Item option.
You can calculate the difference there. The downside is that you will be hard coding the 2 years so unlike using vs Previous or Next it will need automatically pick up the 2 new years next year.