Variance Calculation

[Anonymous]

Hello,

I created a measure to estimate the variance between "Actual" and "Budget" expenses. The equation is

Variance = calculate([EBITDA], filter('values',[EBITDA]<>0),filter('values','values'[Scenario] = "Actual")) - calculate([EBITDA], filter('values',[EBITDA]<>0),filter('values','values'[Scenario] = "Budget"))

The data table example:

Account	Scenario	Values	GDR Type
1000	Actual	100	COR
1000	Budget	100	COR
1000	Actual	100	Revenue
1000	Budget	100	Revenue

The measures created:

EBITDA = [Gross Profit]-[SGA]

SGA = SUM('values'[SGA Value])

Revenue = sum('values'[Revenue Value])

The variance calculation in the measure works well but how do I create a Viz with Actual, budget, and variance in one matrix?

Account	Actual	Budget	Variance
1000	100	100	0

I have two viz one with Actuals and Budget and the other with variance only. The Actual and Budget have Ebitda.

Thanks

[amitchandak]

@Anonymous , Did not get it completely. But if you do want variance as roe you have to something like this

  Union (
Summarize(filter(Table,Sceniro="Budget"),Table[Account],"Name","Budget","Value",Sum(Table[Value])),
Summarize(filter(Table,Sceniro="Actual"),Table[Account],"Name","Actual","Value",Sum(Table[Value])),
Summarize(Table,Table[Account],"Name","Variance","Value",Sumx(Table,if(Sceniro="Actual",-1*Table[Value],1*Table[Value])))
)

Or better

  Union (
Summarize(Table,Table[Account],Table[Scenario],"Value",Sum(Table[Value])),
Summarize(Table,Table[Account],"Scenario","Variance","Value",Sumx(Table,if(Sceniro="Actual",-1*Table[Value],1*Table[Value])))
)

[Anonymous]

Thank you for the reply and sorry for the confusion. To further clarify: I am trying to get Actual, Budget, and Variance in one table.

To display Actual and Budget, I am using EBITDA = [Gross Profit]-[SGA] measure but for variance, I am using Variance = calculate([EBITDA], filter('values',[EBITDA]<>0),filter('values','values'[Scenario] = "Actual")) - calculate([EBITDA], filter('values',[EBITDA]<>0),filter('values','values'[Scenario] = "Budget")) measure.

Due to two different measures, I end up getting two different Viz. One for Actual and Budget and one for Variance. I want all of these two to be in one Viz.