Pivot Tbl Calc Field using %'s

G

gramos14

My underlying data table has 20k rows similar to this:

Customer % of Cost Cost $
Customer 1 10% $100
Customer 1 30% $400
Customer 1 60% $500
Customer 2 30% $300
Customer 2 70% $800

I created a pivot table with a calculated field with this formula (% of Cost
* Cost $). The calculation works fine in the pivot at the lowest level, but
the sum of all customers is incorrect. It seems the calc field sums all the
%'s and all the Costs first and then does the calculation.

Any ideas how I can get the calc field to multiply each % and associated
cost first and then sum all the results together?
 
S

steven1001

Try creating a helper column for the margin$ and then use a Calculate
Field to calculate the % as Margin$/Cost$ (or whatever you need to d
to get the correct%)

regards.
 
J

John James

Hi gramos14,

You are right! The individual amounts for the calculated field in th
data area are correct but the totals don't add these individua
amounts! Instead they add the individual components of the formul
before multiplying. Surely this is a bug because it makes no sense.
Does anyone know whether it is being fixed?

If you want totals (which of course you do) I assume you need to do al
calculations in your source data area. This calculated formula facilit
appears essentially unusable.

Thanks for letting me know
 
S

steven1001

the four columns you nominated extended to 20,000 rows created a file of
1.13mb
Adding an extra column increased it to 1.73mb

adding a pivot table and choosing not to store the pivot table data
still gave a file size of 1.73mb .. a fairly trivial size these days.

If you want to keep the file at 1.3mb then perhaps you could change the
data you are given so you actually receive a cost and revenue column
rather than a cost and a % column.

good luck.

regards...
 
Top