Forum Discussion

HL_Office's avatar
HL_Office
Copper Contributor
Jun 28, 2021

Pivot - Adding calculated fields between value-fields

Trying to create calculated fields between the value-fields of a Pivot-table. Eg I have sales data of the past 10 years for all customers and products. Now filter the last 3 years (eg 2019, 2020 and 2021) and would like to have the Pivot-table to show, besides he actual sales data, also the difference between years (both in absolute values and in percentages). And if I change my filter of years then I'd like the pivot-table to still make the comparisons with Year0 and Year-1 et.c.

Cannot figure this out...

    • HL_Office's avatar
      HL_Office
      Copper Contributor

      Thanks for your response but this isn't what I'm looking for (my bad, sorry).

      Made an example of a pivot-table which I've got and added the 'current year vs. previous year' calculations to the right of the pivot (yellow circle). 

      My question is: Can I somehow make calculated items / fields in pivot table which show me these differences? And would these calculated fields be smart enough that if I choose to filter on eg years 2019 and 2020 in pivot then the only comparison I'd see is Delta 2020 vs. 2019? 

      • SergeiBaklan's avatar
        SergeiBaklan
        MVP

        HL_Office 

        Depends on how your source data is organized. As variant that could be

        which gives result as on your screenshot. 

Resources