Problems with Pivot table

Hi,

I have been searching for ages for a resolution to this issue. I want to insert a calculated field into a PT that adds up dollar margin that has been sorted highest to lowest, to give a cumulative % of the total margin in the table. I have tried everything, including adding an outside helper column however this is not working.

I want to be able to have it so that regardless of how the data is sorted, the cumulative column starts with the first cell in the table, & adds the values as it goes down. For example total margin in the attached example is $285 out of $563 total, so it shows 50.6% as the percentage of total margin. The formula would then add the next line of sorted margin to the cumulative total and express as a percentage. In this example it would be $285 + $241 = $526, which as a cumulative percentage of total is 92.8%.

I really need to have it as a calculated field as I want to be able to manipulate the data and dependent upon the field the data is sorted on, need to have a progressive margin % that can be conditionally formatted.

The attached example is in Excel 2007.

Any help would be appreciated.

Thanks very much for the quick response I was not using the SUM formula you have shown me however that has helped. My only question now is how do I manage to make the formulas work in the Cumu Margin column when I sort the PT? In the attached I have expanded rows to show products, and the numbers in the helper columns are distorted. Similarly if I just choose Private Label or Contract from the Channel list, it has a formula which goes to row 7 regardless of the size of the data set, which shows a total of 200%. This is why I thought if there was some way to incorporate this column as a calculated field it might assist the process?

Originally Posted by vlady

hello
can you try..

margin

=IFERROR(SUM(GETPIVOTDATA("$ GM",$A$3,"Item Description",$A$5),GETPIVOTDATA("$ GM",$A$3,"Item Description",A5)),"")

I have tried this thanks (see attached) yet it is not giving me the correct numbers in the GM column?????????

Greta thanks! I am at work at the moment but will look a lot closer tonight, seems to have done the trick I hope!!! Out of curiosity, why do all the formulas pick up cell $a$3 in the PT given it is a blank cell??? Seems a little strange - just wondering