Pivot table column creation: Mission Impossible ?

[mrchong]

I have a table with these field headers Product/Status/USD:-
A/Actual/100
A/Budget/90
A/Variance/10

I have created a Pivot Table with
Product as row field (A or B)
Status as Column field (Actual, Budget, Variance)
USD as Value

It looks fine like this but I need to create an additional column called
Variance % which is (Variance/Budget x 100%).

Please urgently advise how to create this new % column within Pivot Table.

Thanks.

 

[Gary Brown]

You need to insert a Calculated item.

Highlight Status in you Pivot table column and select Pivot Table
Formulas, Calculated Item from the Pivot Table Tool bar, Give th
formula a name (ie Variance%), then click on the Status Field in th
Fields window, and then click on Variance in the Items window and clic
on Insert Item. In the formula bar edit this with a / at the end of th
formula and then click on Budget from the items pane and click o
Insert item, finally edit the formula with *100 and click on OK.

You should now have a pivot table with a variance field. You may nee
to format accordingly.

Good Luc

 

[John James]

You can achieve the result by adding a field to your table, and the
create a pivottable on the extended selection.

Calculated field sometimes produces "unexpected" totals

 

[mrchong]

Many thanks. However this method only works if there is one VALUE Column
"Actual", another VALUE Column of "Budget".

My case is there is only ONE value column called US$, and there is a
dimension called Status which has values of Actual or Budget.

How could I proceed ?

Pls help....

 

[Gary Brown]

You should still be able to do this even though you only have one value
column.

I have attached an example to show you what I mean


+-------------------------------------------------------------------+
|Filename: example pivot.zip |
|Download: http://www.excelforum.com/attachment.php?postid=4659 |
+-------------------------------------------------------------------+

 

[mrchong]

Many thanks.
However for some reason I cannot access to that file. Could you please email
to me to
(e-mail address removed) ?

Thanks !!!

 

[mrchong]

Many thanks !! You did it !!! Could you please advise how you did it step by
step ?

I found no calculated items or calculated field. How to produce this
additional column ??

Thanks.

 

[mrchong]

Oh I got it now. Thanks !!!