calculated field in pivot table divide one column by the other EXCEL

Question

I have two columns in a pivot table. Count of Work orders, and Sum of the Cost. I want to insert a calculated field that simply divides the sum of cost by count of work orders to get an average per work order.

When I put I insert a calculated field with the following formula, it yields the total cost, not the average. You'll see the fields are subtotal (cost) and WO#(work order)

And here is what the output looks like in my pivot table.

Answer 1

When you add a calculated field in a pivot table, you need to only add the reference, not a calculation inside of it, so you don't need to add Sum or Count in your definition. However, Excel works calculated fields in a very infuriating manner - first it adds your values and then performs the calculation - if, for example, I have a calculated field that's simply field3=field2/field1, when I want to display the SUM of these values, instead of sum(field3), it does sum(field2)/sum(field1)

I would recommend doing this calculation outside of the pivot.

For example, see my results when I have the following table as input for a pivot