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

[ahn_photos]

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 selected years (e.g. '21 vs. '19). E.g. the calculated field for growth of 2021 vs. 2020 is : 2021/2020-1

At a monthly level, it works perfectly, but when I view the grand total, growth is reflected as zero (see bottom right). Why would that be?



Thanks in advance
AHN

 

[ahn_photos]

In the above, for Total '21 vs. '20, the value should be 8.23%

 

[ahn_photos]

Kindly allow me to better explain the situation with some more details.

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.

Thanks