I have a field called "sum of purchases". I can set the pivot up to display this field correctly as a % of the column total. However, I want to be able to setup a separate calculated field as "% of sum of purchases". I think I need to do this so that I can use this new field in another calculation which would then multiply a "Rebate" field number by the percentage. However, I'm struggling to get the first new field to compute correctly.
Here's my formula for the calculated field for "% sum of Purchases":
=IF(ISNUMBER('Sum of purchases'/ SUM('Sum of purchases')),('Sum of purchases'/ SUM('Sum of purchases')),0)
What's wrong with this picture? Why can't I get the rows to display as a component percentage which would sum up to the column total at the bottom? Np to do this with the original field set to show values as % of column total, but as I outlined above, I'm thinking I need to setup the calculated field separately ... so it can be included in a second calculation.
Any ideas?
Thanks!!
Steve
p.s., also posted here:
One response indicated this can not be done ... I'm now playing w/ Power Pivot to see if that might offer a possible solution.
pps, on Excel 2010 and win 7
http://answers.microsoft.com/en-us/...field-in/807185c8-34f3-42c6-866e-a096e306b41b
http://www.mrexcel.com/forum/excel-...-setting-up-calculated-field-pivot-table.html
http://www.excelforum.com/excel-gen...g-up-a-calculated-field-in-a-pivot-table.html
Here's my formula for the calculated field for "% sum of Purchases":
=IF(ISNUMBER('Sum of purchases'/ SUM('Sum of purchases')),('Sum of purchases'/ SUM('Sum of purchases')),0)
What's wrong with this picture? Why can't I get the rows to display as a component percentage which would sum up to the column total at the bottom? Np to do this with the original field set to show values as % of column total, but as I outlined above, I'm thinking I need to setup the calculated field separately ... so it can be included in a second calculation.
Any ideas?
Thanks!!
Steve
p.s., also posted here:
One response indicated this can not be done ... I'm now playing w/ Power Pivot to see if that might offer a possible solution.
pps, on Excel 2010 and win 7
http://answers.microsoft.com/en-us/...field-in/807185c8-34f3-42c6-866e-a096e306b41b
http://www.mrexcel.com/forum/excel-...-setting-up-calculated-field-pivot-table.html
http://www.excelforum.com/excel-gen...g-up-a-calculated-field-in-a-pivot-table.html