Subtract two columns in a pivot table

[#1]

I have made a pivot table where i need to subtract the two scenarios Budget and Actual to be displayed in a Remaining column.

I have tried to add an expression (which works in my straight tables) but that results in two columns below budget and two below actual. I have also tried to 'Add a calculated dimmension' but that gives me an error. 

Project	Actvity	Scenario:	Budget	Actual	Remaining
Project 1	Activity 1		30	10
	Activity 2		40	30
	Activity 3		10	15
TOTAL			70	55
Project 2	Activity 1		5	5
	Activity 2		25	20
	Activity 3		30	30
TOTAL			60	55
		TOTAL	130	110

Any suggestions?

Thanks!

[Sokkorn]

Hi,

In Remaining expression use this formular = [Budget] - [Actual]

Regards,

Sokkorn

[#3]

When I use the expression:

sum({<Scenario={'Budget'},Amount>}Amount)-sum({<Scenario={'Actuals'},Amount>}Amount)

it creates two columns below Budget and two below Actuals (it doesn't appear like i wish when i drag and drop it somewhere else). Something wrong with my formula?

[SunilChauhan]

column(budgetexpressionno)-column(actualexpressionno)

where expressionno is recognizes like below

for first top expression->column(1)

for second-> column(2)

;;;

so on

[#5]

Back in business!

Sunil,
I have only got 1 expression: if(Scenario ='Budget' or Scenario ='Actuals' ,sum(Amount))

which displays one column with budget and one with actuals.

Is it possible to use COLUMN? Any other way to do it?

[SunilChauhan]

no its not possible

but  You can write like below to substract

sum( if(Scenario ='Budget' ,Amount))

-

sum( if(Scenario ='Actuals',Amount))

[#7]

If i use:

sum( if(Scenario ='Budget' ,Amount)) - sum( if(Scenario ='Actuals',Amount))

I end up with 4 columns:

Budget | Budget - 0| Actuals | 0 - Actuals

when I want:

Budget | Actuals | Remaining

Hmmm.....

[SunilChauhan · Accepted solution]

in first expression

sum( if(Scenario ='Budget' ,Amount))  fro Budget

in second expression

sum( if(Scenario ='Actuals',Amount)) for Actual

in third expression

sum( if(Scenario ='Budget' ,Amount)) - sum( if(Scenario ='Actuals',Amount))

or if want like

Budget | Actuals | Remaining

in same expression

sum( if(Scenario ='Budget' ,Amount)) &'|'&sum( if(Scenario ='Actuals',Amount)) &'|' &

sum( if(Scenario ='Budget' ,Amount)) - sum( if(Scenario ='Actuals',Amount))

[#9]

Thanks Sunil! You have been very helpful and patient!