Forum Discussion
HL_Office
Jun 28, 2021Copper Contributor
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...
That's with data model and DAX measure. They could be relatively simple, everything depends on how your data is structured. Bunch of possible patterns is here Standard time-related calculations – DAX Patterns
- HL_OfficeCopper 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?
Depends on how your source data is organized. As variant that could be
which gives result as on your screenshot.