Calculated Item; wrong sub and grand total with percentages

[Daktari]

Hi,
As the head line hints at, I'm having some issues with the Calculated Item function in Excels pivot.
I'm creating a report with the current and previous year, where I calculate the years sum as a percentage of yesteryears.

It works row by row, but for each total it adds all the row totals in one large percent.
IE if two items sold 3 units each last year, but sold 6 units this year, the calculated item subtotal shows 400% (not 200%).

Is there a method in getting the pivot to show current percentage totals?

The calculated field option is a no go due to how I'm getting the data (different databases, overlapping sets, huge sets, got to keep the size down etc).

I heard that this is a "known" Excel bug, but I wont take rumors for fact before checking with you.

 

[Jerry Sullivan]

Hi Daktari,

Using a Calculated Item isn't a good fit for the Year Over Year comparison you are trying to do.

A better method would be to add a second instance of the "Sum of Units Sold" field to the Values area of the report,
then use the Show Values as...> Calculation: % of > Base Field: Year > Base Item: (previous).

These two links provide similar examples with one having the timescale oriented vertically and the other horizontally.

Excel Magic Trick 612: Pivot Table Year Over Year Sales Calculation From Dates Listed As Text - YouTube

How to show monthly values & % changes in one pivot table - Excel Pivot Table Examples | Chandoo.org - Learn Microsoft Excel Online

The problem you describe with trying to do this using Calculated Items is a good reason why those aren't a good tool for your purpose but it's not a bug.
A bug would imply that subtotals and grand totals of Calculated Items do not behave as intended and designed which isn't the case.