Period over period difference on percentage of total calculated field not giving expected output for subtotals and totals

[navi]

Mix CHange Cal - Total Subtotal Incorrect

I am trying to calculate period over period difference of a percentage of total calculated field in a pivot table. It works well in the individual rows but not giving the right result in subtotal and totals. Below are the details of the calculations I’m using :

Measure
Mix % → percentOfTotal(sum(Measure),[Week])
WoW % Change → (periodOverPeriodDifference({Mix %},Week,WEEK,1) * 10000)

Can anyone please suggest what I am missing here ?

Thanks !!

[apjvinod]

Hi @navi

Welcome to QuickSight Community . And thanks for creating the sample analysis in Arena for us to understand better .

Please use periodOverPeriodPercentDifference function if you want to measure WoW%change like below .
periodOverPeriodPercentDifference(sum(Measure),Week,WEEK,1)

You may refer to this modified Arena sample analysis for your reference .
31730-Community-Question

And please note periodOverPeriodPercentDifference has to be computed separately for each level , since its non-additive measure .

For instance for the week of 4-May-2024 and for A_2
CC1 – WoW%change – 240.78% (20743-6087 ) / 6087
DD1 – WoW%change – 258.72% (2198294-612818 ) / 612818
for A_2 (subtotal) – WoW%change --258.54% (2219037-618905 ) / 618905

Did this solution work for you? I am marking this as, “Solution,” but let us know if this is not resolved. Thanks for posting your questions on the QuickSight Community Q&A Forum!

Thanks
Vinod

[navi]

Thanks Vinod for looking into it, but I was trying to get WoW % Change based on how much is measure % to total.

I revised the calculation Mix % using this formula → sum(Measure)/sumOver(sum(Measure),[Week])

and this fixed the issue. I have also updated the dashboard Mix Change Cal - Updated

Sharing this in case anyone is looking for solution