Pivot - show as % of sub-total

[Me!]

Hi,

Is it possible to do the following in a pivot table (the data refers to
cars)?

I have two row fields, 'Manufacturer' and 'Model', with the data values
being a count of model.

So, for example there are five different Audi models, each with a count. I
want to show the model count as a % of the Audi sub-total (not as a % of the
overall total).

This would give each model's total as a % of its respective manufacturers
total.

Is this possible?

Many thanks.

 

[Michael.Tarnowski]

Hi,

Is it possible to do the following in a pivot table (the data refers to
cars)?

I have two row fields, 'Manufacturer' and 'Model', with the data values
being a count of model.

So, for example there are five different Audi models, each with a count.  I
want to show the model count as a % of the Audi sub-total (not as a % of the
overall total).

This would give each model's total as a % of its respective manufacturers
total.

Is this possible?

Many thanks.

Hi,
maybe the use of a helper columns helps.
HTH, cheers Michael

 

[herochenna]

Visit www.ExcelGoodies.Com

 

[Jason]

Hi,
maybe the use of a helper columns helps.
HTH, cheers Michael

Hi,

I'd thought of having an additional column in my source data, but it
would have to include some form of aggregation, which I'd rather do in
the pivot, i.e. I could have a column in my source data which gives the
manufacturer count for each model, and then in the pivot show my model
count as a % of the Manufacturer count.

But there would be a lot of repeated data, i.e. the manufacturer total
for each model would be the same, which is rather inefficient.

SO, are you saying it can't be done in the pivot?

Cheers....Jay

 

[Herbert Seidenberg]

Excel 2007 PivotTable
% of subtotal.
Source gets no helper.
PT gets tricky format.
http://www.mediafire.com/file/jmm5mivzitz/12_17_09.xlsx

 

[Me!]

Hi Herbert,

The link won't open. Can you advise how it's done?

Thanks......Jason

 

[Herbert Seidenberg]

Excel 2007
Checked MediaFire link. Works for others.
Alternate CloudFile link (with even more options):
http://c0444202.cdn.cloudfiles.rackspacecloud.com/12_17_09.xlsx

 

[Jason]

Excel 2007
Checked MediaFire link. Works for others.
Alternate CloudFile link (with even more options):
http://c0444202.cdn.cloudfiles.rackspacecloud.com/12_17_09.xlsx

Got it to download Ok now....thanks again.

Your solution doesn't actually use the pivot table to do it - but
formulas instead. I can think of many formula based solutions but I was
trying to find out if it could be done *using* pivot functionality,

Cheers,

Jaspon
---