Help with calculated field please - my example works, except for at Total level

[ahn_photos]

Hi everyone,

I have a file in which I'm comparing sales between 3 years: 2019, 2020 and 2021. In the pivot itself I want to see 2021 growth versus 2020 and versus 2019.

In order to do that, in the raw data I have included the following columns:
Year, quarter, Month, Category, Sub-category, Sales, 2019, 2020, 2021 (see below and attached)

For columns 2019, 2020 and 2021, the formula returns the value in the Sales column if the value in the Year column is the same, e.g. if in year it's 2019, the sales value will appear in the 2019 column and zero will reflect in 2020 and 2021 columns. I did this so that I could make a calculated field in the pivot table, for example:

Calculated field name: '21 vs. '20
Formula: ='2021'/'2020'-1



At a monthly level, this works perfectly, but when I view the grand total in the pivot, growth is reflected as zero (see bottom right).

In the example, for Total '21 vs. '20, the value should be 8.23% but it's showing 0%:



I will be super grateful if someone can help me figure this out.

Thank

 

[vletm]

ahn_photos
Why did You duplicate Your thread?

Pivot calculated field for YoY growth works for months but zero for Total

Hi folks, Hoping you can advise on an irritating problem that I've encountered working with a calculated field. In the raw data I have : Reason for the separate years' sales is that I want to be able to use a calculated field in the pivot to calculate growth year-on-year and between...

chandoo.org

You could give more details with the original one.
This thread is close.