Pivot table % to Grand total

**mangeshp4** · 08-16-2010, 01:52 AM

I have following pivot table

Product Value
X 30
Y 40
Z 30
Grand Total 100

I know the option of Show data as % of Column. My problem is i want to add 5% of what ever % comes in the calculated % filed. For above example . I would like to add another calculated filed say which will show result as 35 45 35.

Thanks to all.

Regards,

Mangesh

**zbor** · 08-16-2010, 02:01 AM

Add one column in original data where you adding 5% so use that column in Pivot Table

**s1police** · 08-16-2010, 02:04 AM

You can click on the data field, and go to "insert" > "Insert calculated field", and you can create the formula you want.

Cheers,
s1police

**mangeshp4** · 08-16-2010, 02:05 AM

No but in pivot table it will take value while calculating instade of what data is shown as.

**s1police** · 08-16-2010, 02:34 AM

I don't really get yout point, you are talking about add 5% to the column ... so, the method is, you select the % field, and use the formula to add 0.05.
Is that what you want? Or, please give more information about what you want.

Cheers!

**mangeshp4** · 08-16-2010, 03:23 AM

refer the below out put Sum of sales2 is (Show data as % of Col total)
sum of field1 is Calculated field where i want data to come as 19.98% for product A (i.e 14.98%+5%)

Product Sum of Sales Sum of Sales2 Sum of Field1
A 335 14.98% 335.05
B 398 17.79% 398.05
C 485 21.68% 485.05
D 225 10.06% 225.05
E 356 15.91% 356.05
F 438 19.58% 438.05
Grand Total 2237 100.00% 2237.05

**teylyn** · 08-17-2010, 06:42 AM

with reference to the data in your duplicate post:

mangesh,

there are several things in your spreadsheet that don't add up.

If you add up the "expected values", the sum will be 130%, not 105%
In a Pivot table, if you display the line items as %, the total will always be 100%, so you can't really change that in the pivot table.

You can create a column outside of the pivot table, with a formula like
=GETPIVOTDATA("Sum of Sales2",$A3,"Product",A5)+0.05
and copy that down.

Again, the sum of all the results will be 130%, not 105%

There's a flaw in your logic somewhere, as far as I can tell.