I am trying to create a pivot table and pivot chart in excel 2016 based off of cattle auction data. Data for each auction includes: Head of Cattle, total weight, avg weight, price per lb, and price a head. I am trying to show the avg price per head of particular weights. The trouble is when one transaction is for 30 head @ $800 and the next is for 1 @ $700 it shows average price of $750. But I want to show the average weight of the 31 cattle at $796 not the average of the transactions @ $750. Any ideas?
0
votes
1 Answers
0
votes
From what you've laid out in your question, it sounds like you should do some calculations in your data before you pivot it. Basically, make a new column that is [transaction] * [the head of cattle], so in this case the new column values are 24000 and 700, respectively.
From there, you can insert a pivot table and use a custom calculation. While in the pivot table click on 'Options' and then click on "Fields, Items, & Sets". Then set the calculation in the formula bar to your new field divided by head of cattle.
