% of Subtotal in Pivot Table

[ldarling]

Is there a way to change the settings or options in a pivot table to calculate the % of Subtotal. My data is arranged by month for 18 months so I would prefer to do by taking % of Subtotals in the columns

Thanks
[/img]

 

[xenou]

Hi:
Welcome to the board.

I am sorry but to the best of my knowledge customizing calculations with subtotals is not feasible. You may want to reference data in the pivot table elsewhere on the spreadsheet and perform calculations with "regular" excel formulas...

You may be able to use a page field for analysis - depending on how your dates are structured (if the months are a data item by themselves rather than groups of dates this is easier -- only 18 items). Stick the months in a column field, select the months you want to include or not include, then drage the column field up to the page field area - those months you did not check will be excluded and you can get a percent of column for the subgroup now. You can also do this directly in the page field by right clicking the page field item, and then selecting field settings..., and then using the hide items option. Subtotal hidden page items is an option in table options but it is usually defaulted to No.

Hope this helps...I wish I could offer better advice. Regards.

 

[ExcelChampion]

Perhaps this won't work, but worth a look...

On the Pivot Table toolbar click the Pivot Table button.

Find and select Formulas.

Find and select Calcualed Field.

Refer to the help files if you need help from there or post back.

HTH!