Pivot Table field not calculating correctly

I have a pivot table based on data that has a numerator, a denominator and a rate (Numerator/Denominator*Rate Modifier). I have no issues with that. However, as the Pivot Table aggregates the data when my report filters are changed, the rate does not figure correctly. It sums up all of the rates together, rather than giving me the rate based on the numerator and denominator (which can be summed without issue) for that filter.

I have tried removing the rate field that draws from the data table, but I cannot figure out how to get a column that calculates that rate based on the numerator and denominator showing in the Pivot Table. I tried adding in a calculated field, but I get the same answer as when the rates are summed. I have tried changing the "Summarize value field by" in the "Value Field Settings" (I changed from Sum to Product), but that didn't do what I needed it to. What is need is for it to do a basic Excel calculation, i.e., =(C9/D9)*1000.

I have attached a small sample file.

I am hoping that I am just missing a small step.

Thank you.

Why not just put your =(C9/D9)*1000 outside the Pivot Table in F9?

You could delete the Rate column from the PT as it is a meaningless figure.

Regards,

David

Thank you, David. This is only a small portion of this Pivot Table. At the end of the day, I will have approximately 40 metrics with 2 rates per metric. I will also have to chart these metrics over time. As locations changes, applicable metrics will also change. I really need for the metric to update within the pivot table.