percentDifference between two rows in Pivot Table

[Agatha]

Hi,

I would like to create a YoY calculation in my pivot table to see the percentage difference between 2022 and 2023. I have created a calculated field “YoY changes” with the formula:

percentDifference
(
sum( {cakes} ),
[year DESC],
1
)

However, because it is looking at the current value and the previous one it is comparing Pink from 2023 to Blue from 2022 (in red in the screenshot) which I don’t want.

Is there a way to say we only want to compare the first and second row (instead of the current and the previous one with a value)?

Many thanks,
Agatha

[Max]

You can partition by that group.

percentDifference
(
sum( {cakes},[{color}] ),
[year DESC],
1
)

Does that work for you?

[Agatha]

Hi Max,
I get the following error “Field NESTED_SUM(“cakes”, “color”) must be aggregated for table calculations.” when trying to save the calculated field with your solution.

[Max]

Can you try wrapping it in a sumOver?

percentDifference
(
sumOver(sum({cakes}),[{color}] ),
[year DESC],
1
)

[Agatha]

@Max

sumOver

almost there! Now I just get the error “Field “cakes” must be aggregated for table calculations.” - do you know how i can fix that?

[Max]

Did you paste in my exact function?

You need to wrap the cakes in a sumover and sum

[Agatha]

Sorry I hadn’t wrapped the cakes in a sum. I now have copied and pasted the exact function, i was able to save the new calculated field. However, I still have the same issue in the pivot table: I have a “YoY Changes” for Blue in the 2022 row (as it’s comparing Blue in 2022 with Pink in 2023). The percentages also now seem off. Please find below what I have now (with real data this time):

[Max]

Hmmm,

can you instead try partitioning by the percent difference?

percentDifference
(
sum( {cakes} ),
[year DESC],
1,
[{color}]
)

[Agatha]

Perfect it worked! Thank you so much!!