Morning all,
I have a pivot table that is approx 15,000 rows long by 11 rows wide. The rows are split by 5 business areas and then a unique item reference. The columns are unique references to where the items are to be allocated.
I have a financial value for each reference and an item count. As each item is entered on a separate row in the source data table with the total financial value for all items under that reference I have created a calculated field to divide the financial value by the item count to give me an accurate total item reference number value (to avoid double counting).
I now need to come up with sub total for each business area by the column reference, I'm aware that the subtotal and grand totals for calculated fields still apply the formula used to calculate each value. Is there anyway around this? I really don't want to have to manually calculate 55+ subtotals as the data set will be changing regularly...
Please help!
I have a pivot table that is approx 15,000 rows long by 11 rows wide. The rows are split by 5 business areas and then a unique item reference. The columns are unique references to where the items are to be allocated.
I have a financial value for each reference and an item count. As each item is entered on a separate row in the source data table with the total financial value for all items under that reference I have created a calculated field to divide the financial value by the item count to give me an accurate total item reference number value (to avoid double counting).
I now need to come up with sub total for each business area by the column reference, I'm aware that the subtotal and grand totals for calculated fields still apply the formula used to calculate each value. Is there anyway around this? I really don't want to have to manually calculate 55+ subtotals as the data set will be changing regularly...
Please help!
