# Calculated Item; wrong sub and grand total with percentages

#### Daktari

##### Board Regular
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.

### Excel Facts

Return population for a City
If you have a list of cities in A2:A100, use Data, Geography. Then =A2.Population and copy down.

#### Jerry Sullivan

##### MrExcel MVP
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.

Replies
0
Views
767
Replies
1
Views
148
Replies
1
Views
532
Replies
0
Views
647
Replies
2
Views
253

1,195,598
Messages
6,010,642
Members
441,558
Latest member
lambierules

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

### Which adblocker are you using?

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

### Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back