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

[John James]

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.

[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" 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
>
>

[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

[mrchong]

Many thanks.
However for some reason I cannot access to that file. Could you please email
to me to
hkappleorange@yahoo.com.hk ?

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

[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.

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

[mrchong]

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