Excel Pivot Table Calculated Field

Question

I've a simple table with some amounts by date and product name.

Month | Product | Amount
-------------------------
05-'12| Prod A  | 10     
05-'12| Prod A  | 3      
05-'12| Prod B  | 4      
05-'12| Prod C  | 13     
05-'12| Prod C  | 5

From this table I've derived a Pivot table with SUM(Amount) displayed as % of column total.

Month | Product | SUM of Amount
--------------------------------
05-'12| Prod A  | 28.89%           
05-'12| Prod B  | 8.89%        
05-'12| Prod C  | 62.22%

So far So good... Now I want to add an extra column to my Pivot table which gives me the outcome of the percent values in the sum amount field times 1000. Adding a calculated value =SUM(Amount)*1000 is not giving me the right values. Excel gives me.

Month | Product | SUM of Amount | Field 1 
---------------------------------------------------------
05-'12| Prod A  | 28.89%         | 13,000                     
05-'12| Prod B  | 8.89%          | 4,000                 
05-'12| Prod C  | 62.22%         | 28,00

It isn’t taking the percent values but the sums of the amounts and times that by 1000. I could do a simply formula outside of the pivot table but the lines of the table move around a lot so it would be better to have it done inside the table. How to achieve this?

Thank you in advance.

user1965813 user1965813 · Accepted Answer · 2013-06-26T13:57:19

The values you get are the actual sums time 1000. To get what you want, you need to simply give the figures from the first column the appropriate format (*1000). This you can achieve via #'%%%%, however this does display a lot of percentage signs, where only one would be sufficient.

Alternatively just store the Percentage that each entry contributes in the data and use that.

Excel Pivot Table Calculated Field

1 Answers