referencing a sum with a calculated field

[miconian]

I have a pivot table that looks like this:

productname     impressions     cost        CPM
product1          1,000,000        4523      4.7       
product2          5,000            2400     48.45
product3          3,000,500        6,000     3

CPM is a calculated field that uses the formula =cost/(impressions/1000)

The problem is that the numbers in the CPM field are not, in practice, the results of that formula. They are averages (since it's a data field, I had to choose whether it was to be a sum, average, etc.). This means that, for example, the CPM 4.5 on the first row is not a calculation using the two numbers to its left. It is the average of the output of thousands of calculations done on the raw data, in which every line item with a productname of product1 had its CPM calculated, and then all those output CPMs were averaged together.

This is not what I want. I want a field that calculates based on what's already in the pivot table. So that the formula would be more like:

=(sum of cost)/(sum of impressions)/1000

Thoughts? Let me know if I need to clarify. Seems to me this must be a common problem.

thanks!

 

[buxtongt]

I'm not sure I understand the question fully, but could you just put the formula(s) outside of the pivot table (using the numbers in the table)?

 

[miconian]

Technically yes, but that causes other problems. There will be many pivot tables, and they will all be changed frequently, with fields and rows added and taken away. The formulas outside the pivot table would have to be constantly re-created, copied into new cells, moved, etc.