Excel 2007 : Pivot Table problem

**seeker101** · 06-08-2012, 06:00 PM

I have created a pivot table with column values for sales, cost and gross margin (GM) and gross margin percentage (GM%) sorted by customer. There are 65K rows of data. Unfortunately, the pivot table will not return the correct value by customer of the GM percentage. In the database, sales, cost and GM are values, whereas GM% is formula driven and in the pivot table the field setting for GM% is set on "AVERAGE". Is there anyway to get the pivot table to display the GM% correctly?

Thanks for any help.

**JosephP** · 06-09-2012, 07:29 PM

can you give a specific example? I reckon you probably just want to use a calculated field in the pivot table rather than the underlying formula column in the data.

**FDibbins** · 06-09-2012, 08:18 PM

i had a look at "calc'd" fields in PT's and couldnt find any options that would give you a %. if your PT is not too long, what i have done in the past is add a calc outside the PT that is based on values INSIDE the PT, and built the formula in such a way that is the PT doesnt go down very far, the answers were hidden. i copied the formulas down way past where i thought the PT would extend to.

Not sure if this something that will work for you, but its worth a try?