Subtotals of My Calculated Field (Named: % Month Achievement) are showing wrong results

Hi

I am getting wrong subtotals and grandtotals in my calculated field which I have named is %_Month_Achievement.
The pivot table is located on the sheet named Micro Analysis).

Please help.
Find the attached excel workbook named: BSR

I'd be inclined to add another column to the data

Formula:

Please Login or Register  to view this content.

then pick up that column in your Pivot table

Originally Posted by sumiet23

Thanks Richard, but I would rather like to keep the source data columns as least as possible.
And even if I do add the column suggested by you, it would not be usable for different months which are going to vary and add up further in the data set.

Why would it make any difference what the month is and why would it be unusable?
A Pivot table is perfectly capable of filtering any of the fields including a date field, and will correctly report percentages in PT totals and sub totals.

As far as I'm aware a calculated field in a Pivot Table only works with the individual rows and not any total or subtotal rows.
If you're not happy with incorporating a helper field in the data then the only thing you can do is create formulae outside the pivot table that refer to cells in the PT.

Hi,

Just pick any cell outside the PT and add a normal formula referring to specific cells in the PT

e.g. in your case

=D18/E18

But I'm still struggling to understand your reluctance to having one additional helper column in your data.
If it's a matter of presentation just add a column of formuale and then hide the column.