% of subtotal needed in pivot table

[Guest]

Hello, I am working on one of my first pivot tables and need help getting a
calculation of % a row subtotal in a column instead of % of grand total.

We have over 300 stores using a cumulative total of over 400 vendors and
would like to get a column of the total $ purchsed through each vedor by
store and its percentage of the total purchased by that store. The pivot
report that I have so far has a lyout with each store in the first row
position, all of the vendors used by each store in the second row position.
The column is 1 month and the data so far is sum of purchases. I can get a
data field with percentage of grand total but what I need is percentage of
each store's total.


Any help would be greatly appreciated.
Regards,
Mike

 

[Brian Taylor]

Pivot tables do not handle this inheritly (and it is a real weakness).
You have to put that information in your underlying data table. Add a
column that has a sumif formula in it to add up the subtotal values.
Then back in the pivot table you can create a calculated field based on
the original field and the subtotal field. Its the only workaround I
have found to work, but it isn't a good one.

 

[Guest]

I added another field to the source table with the sumif you suggested. Then
I created another pivot table alongside my original in order to get a
calculated field. I also had to filter and offset the calculations on the
subtotal rows by 1. I'm not sure if that's as easy as what you had in mind,
but it worked.
Thanks for your help