How to achieve the following report ?

[#1]

Hi guys,

How to achieve the following report

	MTD Actual	MTD Budget	Variance (MTD Actual - MTD Budget)
A actual	1	0
B actual	2	0
C actual	3	0
A budget	0	10
B budget	0	20
C budget	0	30

Challenge is:

Where the MTD Actual has value, MTD Budget is 0

So, A actual(MTD Actual) - A budget(MTD Budget) is the value the variance column need

Please help how to achieve it.

I am using pivot table.

Thanks.

[alexandros17]

I Suppose that A, B, C are codes so you could link the tables containig Actual and Budget values by codes , in this way use the field of code as dimension.

Let me know

[Anonymous]

instead of your Dimension which holds 'A actual' etc. make a calculated dimension

= left(yourdimension,1)

then it should Show 3 rows and you can define your third Expression as column(1)-column(2)

[#4]

Thanks for your reply.

MTD Actual and MTD Budget are dimensions. So how can i add Variance in the same ?

[Anonymous]

try: sum([MTD Actual] -[MTD Budget])

why do you define measures as dimensions? what is the purpose?

[#6]

MTD Actual and MTD Budget are the dynamic column names coming.

The purpose is for A actual and so on for this fields are derived from some expressions.

sum([MTD Actual] -[MTD Budget]). So this wont work.