Pivot Table - Getting Correct Profit %

[Djblois]

I am trying to get the correct Profit % in pivot Tables but it is not working. Here are the issues:

1) If I do an Average of the % column (from the data tab) in a Pvt Table then I lose Weighted Average.

2) If I do a calculated field in a Pivot table With Profit/Amt then I am getting incorrect data when ever it is supposed to be negative (when amt and Profit are already negative they divide into a positive number) or when amt is equal to zero

I am trying to do an advanced Calculated field in Pivot Tables with IF statements but it will not let me. Does anyone have any ideas what I can do?

I have uploaded some sample data.

[Djblois]

Does anybody have any idea here?

[Paul_Hossler]

What do you want for Rows and Columns?

Doing a PT for Average of % just has a nothing PT with only one number 9.169%

I don't see 'Weighted Average' as a column in your data


Maybe --- as a proof of concept --- you can temporaily delete the col's and other data that are not required, and insert a PT that you have so far to see if there are any more ideas?????

Paul