Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

Variance Calculation

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:

AccountScenarioValuesGDR Type
1000Actual100COR
1000Budget100COR
1000Actual100Revenue
1000Budget100Revenue

 

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?
AccountActualBudgetVariance
10001001000

 

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

 

Thanks

2 REPLIES 2
amitchandak
Super User
Super User

@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
Not applicable

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.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.