Pivot Tbl Calc Field using %'s

[Guest]

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?

 

[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.

 

[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

 

[Guest]

Thanks for the info. I could add more data to my data sheet, but with 20k
rows I was hoping there was a solution to my problem without having to
increase the size of my file.

Greg

 

[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...