Pivot table column creation: Mission Impossible ?
 

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.

Pivot table column creation: Mission Impossible ?
 

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 the
formula a name (ie Variance%), then click on the Status Field in the
Fields window, and then click on Variance in the Items window and click
on Insert Item. In the formula bar edit this with a / at the end of the
formula and then click on Budget from the items pane and click on
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 need
to format accordingly.

Good Luck


--
Gary Brown
------------------------------------------------------------------------
Gary Brown's Profile: http://www.excelforum.com/member.php...o&userid=17084
View this thread: http://www.excelforum.com/showthread...hreadid=534087

Pivot table column creation: Mission Impossible ?
 

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

Calculated field sometimes produces "unexpected" totals.


--
John James
------------------------------------------------------------------------
John James's Profile: http://www.excelforum.com/member.php...o&userid=32690
View this thread: http://www.excelforum.com/showthread...hreadid=534087

Pivot table column creation: Mission Impossible ?
 

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" wrote:


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 the
formula a name (ie Variance%), then click on the Status Field in the
Fields window, and then click on Variance in the Items window and click
on Insert Item. In the formula bar edit this with a / at the end of the
formula and then click on Budget from the items pane and click on
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 need
to format accordingly.

Good Luck


--
Gary Brown
------------------------------------------------------------------------
Gary Brown's Profile: http://www.excelforum.com/member.php...o&userid=17084
View this thread: http://www.excelforum.com/showthread...hreadid=534087

Pivot table column creation: Mission Impossible ?
 

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 |
+-------------------------------------------------------------------+

--
Gary Brown
------------------------------------------------------------------------
Gary Brown's Profile: http://www.excelforum.com/member.php...o&userid=17084
View this thread: http://www.excelforum.com/showthread...hreadid=534087

Pivot table column creation: Mission Impossible ?
 

Many thanks.
However for some reason I cannot access to that file. Could you please email
to me to
?

Thanks !!!


"Gary Brown" wrote:


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 |
+-------------------------------------------------------------------+

--
Gary Brown
------------------------------------------------------------------------
Gary Brown's Profile: http://www.excelforum.com/member.php...o&userid=17084
View this thread: http://www.excelforum.com/showthread...hreadid=534087

Pivot table column creation: Mission Impossible ?
 

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.

"Gary Brown" wrote:


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 |
+-------------------------------------------------------------------+

--
Gary Brown
------------------------------------------------------------------------
Gary Brown's Profile: http://www.excelforum.com/member.php...o&userid=17084
View this thread: http://www.excelforum.com/showthread...hreadid=534087

Pivot table column creation: Mission Impossible ?
 

Oh I got it now. Thanks !!!



"Gary Brown" wrote:


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 |
+-------------------------------------------------------------------+

--
Gary Brown
------------------------------------------------------------------------
Gary Brown's Profile: http://www.excelforum.com/member.php...o&userid=17084
View this thread: http://www.excelforum.com/showthread...hreadid=534087