Pivot table grand total not working - help!

[ClaireH]

Hello,

I have an ordinary pivot table based on data which includes the following information:

Opportunity name, Margin, Probability(e.g. 40%, rather than 0.4), Invoice month

And in my pivot table I am summarising these per month, with the value fields as a calculated field which is (Probability/100)*Margin.

The table entries are calculating fine, but the Grand Totals on both the rows and columns are not working.

Does anyone know why? Or, even better, can anyone help me fix this?!

I've attached an example table with data and a pivot which gives a good summary of what I'm trying to do.

Thanks!

[Pepe Le Mokko]

Works for me ...

Are they activated in your PT Options - Totals and filters tab?

[ClaireH]

Hello,

Thanks for your response.

Totals are activated and are there, but if you look, the totals are not correct! On the column totals, £29,000 is not the sum of 9 numbers all less than 500, and the row totals, the sum on Account 5 Opportunity 10 is also not correct.

Claire

[Flyboy65]

Claire,

I'm not sure, but could it be that you have the "Sum of Probability - adjusted Margin" field in the Values area instead of the Amount field?

[Izandol]

Regrettably this is the way that Calculated Fields work. Each row will calculate:
(SUM(Probability)/100)*SUM(Margin)
and not:
SUM((Probability/100)*Margin)

You must perform the calculation within the source data table to see the results you wish. Or you may use PowerPivot if you have it.

[ClaireH]

Ok, I think that realisation was dawning up on me too!

Not to worry, and many thanks all for taking the time to respond :-)

Claire