I'd like to use the sales data where the city, date, product and product family match.

So, for an issue sub category of 'I hate your apples' for all apple complaints in London for March 2018, I would calculate the CPM using the sales for Apples in London, March 2018. This sales figure would be against every issue sub category for Apples in London for March 2018, if that makes sense?

It's just that if I add the sales figure against each line of data, the pivot table sums the sales figure which gives an incorrect CPM.

I'm considering using VBA to write a manual table, using GETPIVOTDATA to find the sales figure required and putting that against the number of complaints to then calculate the CPM. But this feels long winded and I'm not entirely sure if I can get that to work...

Is there not a way to create a calculated field that will find the sales data from a different table based on the city, date, product and product range? Or can calculated fields only do calculations on their own data?