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?

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

regards..


--
steven1001
------------------------------------------------------------------------
steven1001's Profile: http://www.excelforum.com/member.php...o&userid=30757
View this thread: http://www.excelforum.com/showthread...hreadid=528399

Hi gramos14,

You are right! The individual amounts for the calculated field in the
data area are correct but the totals don't add these individual
amounts! Instead they add the individual components of the formula
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 all
calculations in your source data area. This calculated formula facility
appears essentially unusable.

Thanks for letting me know.


--
John James
------------------------------------------------------------------------
John James's Profile: http://www.excelforum.com/member.php...o&userid=32690
View this thread: http://www.excelforum.com/showthread...hreadid=528399

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" wrote:


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

regards..


--
steven1001
------------------------------------------------------------------------
steven1001's Profile: http://www.excelforum.com/member.php...o&userid=30757
View this thread: http://www.excelforum.com/showthread...hreadid=528399

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


--
steven1001
------------------------------------------------------------------------
steven1001's Profile: http://www.excelforum.com/member.php...o&userid=30757
View this thread: http://www.excelforum.com/showthread...hreadid=528399