Formula - Return value of combined sales from a piviot table

Hello Everyone,

I am looking to add a manual formula to a pivot table that will return the total sales for a person even though they are reporting to someone different now.
When sorted the pivot table only shows the total of sales/cross sales while on that team which is ok (need to see this to) but also need to see number of sales they have had overall with any other teams they have reported to.

This would be for excel 2010.

See the test workbook for the desired result and break down of intended use.

Thanks in advance.

Hi SVTF,

Solution using Power Pivot will be ok for you?

Regards,
AM

While the above does work - Thank You. From a formatting perspective it does not follow the flow of the piviot table with sorted.

i.e boarders out line .. when you sort you see all the boarder outlines etc. Because the formula is copied down it also shows 0 values when sorted - I corrected this with a CF.

I tried to add the formula to the piviot table so that the format of table would be uniform for all calculated fields - but get an error
References, names, and arrayes are not supported in PivotTable formulas.

Is there another formula that we could use that will give the same result as posted above but we can add to the piviot table?