Wrong Row total Calculation in a Calculated Item Field

[DaanDean]

Hi,

I have a question about a calculated field item (within a pivottable), where the formula is defined as the %difference ((yearnew-yearold)/yearold)) between two other items(non-calculated items). This works good for the per-month calculations, 12 months in a row.

However, the GRAND TOTAL ROW of this item sums-up all the percentages, where of course I want the calculation of this total be the %difference of the GRAND-ROW-TOTALS of the two other calculated items. See the example in the attachments.

Thanks!

[stasinek]

Hello,
I've added a new column of data (see in sheet date). The formula, which calculates the difference between 2015 and 2014 is in the sheet "Formulas in PT"

[DaanDean]

Hi Stasinek,

First of all, thank you for the effort. It really looks great and obviously the calculations do work now. However, the next problem is that the source data (like the data in sheet1, but without the extra column) will be reguarly updated. The pivottable than automatically will update the other months and so on. Even the years will eventually differ.

is this possible withouth the artifically created column? Or should a VBA be created to autom. creat the extra column? (and what iff years differ)

[stasinek]

To me it can be done using the Power Query that will fetch data from your data table. Have you installed add-Power Query?