trouble correctly setting up a calculated field in a pivot table

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

btw ... cross posted:

http://www.mrexcel.com/forum/excel-q...vot-table.html

http://answers.microsoft.com/en-us/o...e-a096e306b41b

Are you sure it is just not possible to do that ?

Thanks,

Steve

Okay, I have PowerPivot setup ... any idea on how to implement within PowerPivot ?

Thanks,

Steve