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.