First timer posting here!
I need to calculate average of material purchase prices, weighted by their mass, while ignoring zero values, in each factory separately. There are several different countries, factories and materials (and material & quality categories), but all the materials are measured in tons and are comparable. I would like to have the data in a pivot chart as it is easy to upkeep and visually presentable.
My data looks somewhat like this:
Country / FactoryID / MaterialID / Mass (tons) / Price ($/ton) / Total cost (Mass*Price)
Country1 / Factory1 / Material 1 / 1000 / 30 / 30000
Country1 / Factory1 / Material 2 / 1000 / 30 / 30000
Country1 / Factory1 / Material 3 / 1000 / 0 / 0
In this case, I am getting result in pivot table that the average material price would be 20 $/ton, when clearly the average price is 30 $/ton. Currently in pivot chart I'm calculating Average of (Total cost / Mass), but it is incorrect.
I was able to calculate the average cost correctly by VBA, but it gets frustrating when I would like to have it easily repeatable, and also filter by other criteria than the factoryID.
Any directions?
I need to calculate average of material purchase prices, weighted by their mass, while ignoring zero values, in each factory separately. There are several different countries, factories and materials (and material & quality categories), but all the materials are measured in tons and are comparable. I would like to have the data in a pivot chart as it is easy to upkeep and visually presentable.
My data looks somewhat like this:
Country / FactoryID / MaterialID / Mass (tons) / Price ($/ton) / Total cost (Mass*Price)
Country1 / Factory1 / Material 1 / 1000 / 30 / 30000
Country1 / Factory1 / Material 2 / 1000 / 30 / 30000
Country1 / Factory1 / Material 3 / 1000 / 0 / 0
In this case, I am getting result in pivot table that the average material price would be 20 $/ton, when clearly the average price is 30 $/ton. Currently in pivot chart I'm calculating Average of (Total cost / Mass), but it is incorrect.
I was able to calculate the average cost correctly by VBA, but it gets frustrating when I would like to have it easily repeatable, and also filter by other criteria than the factoryID.
Any directions?
