Have Percent Differences Roll Up in Pivot Table

Question & Answer
, ,
1

Hi -

How can you make percent difference calculations roll up in pivot tables? Currently the highest levels in a pivot table hierarchy the percent difference total is null. This is because of the calculation being:
percentDifference({calculation1}, [{Date} DESC], 1, [{Group By 1},{Group By 2}])

The problem is how can I show the total in the group by 1 too?

image
image1557×380 85.8 KB

2

Hello @Marky

I used a copy of your calculation to test this and was able to get the pivot table to show the percent difference at both levels. Are you able to share anymore about the data that you used or more about how you set up your pivot table?

Percent diff = percentDifference(sum(Sales), [{Order Date} DESC], 1, [Region, Subregion])

(screenshot 1)

image
image217×336 12.3 KB

(screenshot 2)

image
image425×366 24.4 KB

3

@duncan
I believe the issue is “calculation1” is a distinct count vs a sum. When I have a calculation that is a sum it appears to work. Is there a way to roll up the value with a distinct count?

4

Hey @Marky

You could try pre-calculating the distinct count on your dataset before using it in your analysis.