Average problem with Pivot Table

My Pivot Table is made up of... sales orders as Rows split by individual part numbers, and in the columns I have revenue, costs, GP and average GP %.

The problem is that the Orders average GP % is calculating an average between the rows rather than the grand total of Revenue and GP. Therefore some of the average are really screwed up. For example, some products are sold for a .01 since the customer is purchasing other very expensive parts which gives a GP% for that product of -115500%. When that GP% is calculated into the average everything is screwed up.

Any ideas??

Can you post an example workbook and we'll have a go at it. Click on the "Go Advanced" and then the paperclip icon to upload a sample file.

It also helps if you update your profile to show what version of Excel you are using.

Originally Posted by MarvinP

Can you post an example workbook and we'll have a go at it. Click on the "Go Advanced" and then the paperclip icon to upload a sample file.

It also helps if you update your profile to show what version of Excel you are using.

here is an example...I took it out of format but you will see at the bottom how the averages are developed and where the problem is.

bump...anyone?

Originally Posted by NBVC

You might need to add another column to your original database that shows the "adjusted" GP values so that when you SUM and AVERAGE that column you get the intended results.

hmmm maybe this would work...

How do I find an average of specific order numbers? for example...

11002 $45
11002 $42
11002 $43
11002 $49
11240 $65
11240 $71
11240 $68
18010 $120
10655 $43
10655 $39

if this is my data, what formula should I put in column C to caculate the average of the specific order number 11240? Thanks

Shoot. That was a bad example on my part.
Take a look at the attached document above. The problem is coming from something like this. The order contains a product witha Revenue of .01 and a cost of $20,000 but the complete order has a revenue of $60,000 and a cost of $47,000.

The large negative gross profit % from taht one product is skewing the gross profit % for the complete order.

Suggestions for my pivot table?
Thanks!

bump...help please!