Calculation based on grand total column of Pivot Table

[rawbeanz]

Hello,

I have a set of cost data where we have broken out the costs into 4 categories and we want to do a calculation based off a grand total column. The calculation works with the small dataset attached, however, when I add slicers and manipulate the data, the cells will jump out of the formulas and I can't get that calculation.

I've tried using a calculated field, but I don't want the cost per mile to be shown on all 4 categories. We are just looking at total cost per mile.

Please note the data set is truncated and there are other tractors where we have data on the mileages.

Thank you in advance and I appreciate all your help!

[JeteMc]

Assuming that labor and parts are 2 of the categories and therefore the others could fill columns L:M. The grand total column might therefore be in any between column K, if only one cost category is selected, and N if all four cost categories (stated in post #1) are selected. The following formula could be placed in a column that does not potentially interfere with the pivot table (T in the sample):

Please Login or Register  to view this content.

Let us know if you have any questions.