Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
% of subtotal needed in pivot table
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
% of subtotal needed in pivot table
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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
% of subtotal needed in pivot table
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 "Brian Taylor" wrote: 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
% of subtotal in pivot table | Excel Discussion (Misc queries) | |||
% of Subtotal In Pivot Table | Excel Worksheet Functions | |||
% of subtotal in pivot table | Excel Discussion (Misc queries) | |||
% of subtotal in pivot Table | Excel Discussion (Misc queries) | |||
Subtotal vs Pivot table - or best way | Excel Discussion (Misc queries) |