Pivot Table: adding a percentage field as '% of Subtotal'

**Sauvenix** · 11-21-2007, 04:27 AM

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** · 11-21-2007, 05:59 PM

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** · 11-22-2007, 03:43 AM

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** · 11-22-2007, 08:20 AM

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** · 11-23-2007, 11:17 AM

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** · 10-02-2008, 05:28 PM

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

Aga

**steven1001** · 10-07-2008, 07:12 AM

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

**mrbigbri** · 02-04-2009, 07:42 PM

You're a genius Steve. Thanks a ton.

**incognitos** · 06-17-2009, 09:54 AM

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,