# Microsoft Office Application Help - Excel Help forum > Excel General >  >  Pivot Table: adding a percentage field as '% of Subtotal'

## Sauvenix

Hello, 

In the attached Excel file, there is a pivot table. 

In the Data part of the table, there are two columns. The second is the exact same field than the first one but expressed as percentage (Field settings/Options/Show field as "% of" + "Total".

The problem is: I don't want to express this field as a % of Total ('Grand total') but rather as a % of Subtotal (e.g. Danemark Total).

Is this possible and how should I do it?

Any help is appreciated, thank you!

Xavier

----------


## tuph

In the field properties there's an option to make it "% of" and then select the relevant field - I picked Country. I can make the function work but the %'s are screwy in my version of your workbook, but it might be a starting point for you.

----------


## Sauvenix

Hey, 

Good idea, but that's not it.

If you pick country, then you have to choose a country ('e.g. Denmark).

Then, for each cell of the table, it will calculates the % of units sold by a company in the country  in comparison with... the percentage of units sold by the same company in Denmark.

So in Denmark, you get either 100% (when the company sold something in Denmark) or error DIV/0 (when the company sold nothing in Denmark).

I can't believe there is no possibility to choose '% Of' SUBTOTAL (instead of TOTAL). That's a shitty move!

Can I call SUBTOTALS in formulas maybe?

Cheers and thanks for trying anyway!

Xavier

----------


## Bjornar

> I can't believe there is no possibility to choose '% Of' SUBTOTAL (instead of TOTAL). That's a shitty move!



I agree....  but I have not found a solution to this.

I have made a workaround that might help you get the percentages you want but it involves a column with formula outside the actual pivot table, and I can agree if you think this is a bit dirty, but it works....

Put this in E7 and copy down as far as you think your pivot tabel can expand.

=IF(B7="","",D7/VLOOKUP("*Total",A7:D100,4,FALSE))

I have attached your sheet with the colum added.

----------


## Sauvenix

It does work...and I guess I'll have to use it. 

Can't anyone tell Bill Gates about this and tell me to come here and fix this?!

Thank you, 

xavier

----------


## Matizka

As I have exactly the same problem - I thought maybe sb knows how to overcome it differently; maybe still within pivot itself?

Aga

----------


## steven1001

You can do it in a pivot table with some helper columns.
see attachment.
regards

----------


## mrbigbri

You're a genius Steve.  Thanks a ton.

----------


## incognitos

Hey Steven 101,
I was wondering how you were able to create that "helper colunm" or the extra field called MyTotal. How did you code it? 
I tried seeing if there was a formula attached to it, but i couldn't find anything.

Thanks,

----------


## shg

Welcome to the forum, incognitos.

Please take a few minutes to read the forum rules, and then start your own thread.

----------


## neeraj_kalawatia

> You can do it in a pivot table with some helper columns.
> see attachment.
> regards



This is an excellent solution. Quite helpful. Thanks

----------


## workexcelhelp

Thank you so much- this was a very clear method for something I have been struggling with for a while

----------


## parsasa2500

hi its nice solution but is too long for more complex database

----------

