Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago

Variance Analysis

I have below "values" table:

 

Account

ClientService LineOps GeoYearPeriodScenarioData SourceCurrencyMeasureManagerRVP[Values]CORGDR TotalCOR ValueRevenueRevenue ValueSGASGA Value
1000100FinanceOshkosh20201BudgetInputUSDValueABSDGS400 Revenue0Revenue400 0
1000200AccountingRegina20202BudgetInputUSDValueABSDGS400 Revenue0Revenue400 0
1000100ITOshkosh20203BudgetInputUSDValueABSDGS400 Revenue0Revenue400 0
2000200FinanceRegina20204BudgetInputUSDValueABSDGS200CORCOR200 0 0
2000100AccountingOshkosh20205BudgetInputUSDValueABSDGS100CORCOR100 0 0
2000200ITRegina20206BudgetInputUSDValueABSDGS200CORCOR200 0 0
3000100FinanceOshkosh20207BudgetInputUSDValueABSDGS100 SGA0 0SGA100
3000200AccountingRegina20208BudgetInputUSDValueABSDGS200 SGA0 0SGA200
3000100ITOshkosh20209BudgetInputUSDValueABSDGS100 SGA0 0SGA100
1000200MarketingRegina202010BudgetInputUSDValueABSDGS200 Revenue0Revenue200 0
1000100FinanceOshkosh20201ActualInputUSDValueABSDGS500 Revenue0Revenue500 0
1000200AccountingRegina20202ActualInputUSDValueABSDGS500 Revenue0Revenue500 0
1000100ITOshkosh20203ActualInputUSDValueABSDGS300 Revenue0Revenue300 0
2000200FinanceRegina20204ActualInputUSDValueABSDGS200CORCOR200 0 0
2000100AccountingOshkosh20205ActualInputUSDValueABSDGS200CORCOR200 0 0
2000200ITRegina20206ActualInputUSDValueABSDGS100CORCOR100 0 0
3000100FinanceOshkosh20207ActualInputUSDValueABSDGS200 SGA0 0SGA200
3000200AccountingRegina20208ActualInputUSDValueABSDGS100 SGA0 0SGA100
3000100ITOshkosh20209ActualInputUSDValueABSDGS200 SGA0 0SGA200
1000200MarketingRegina202010ActualInputUSDValueABSDGS100 Revenue0Revenue100 0

 

I created the following measures:

Revenue = sum('values'[Revenue Value])
SGA = SUM('values'[SGA Value])
COR = sum('values'[COR Value])
Blank EBITDA = calculate([EBITDA], filter('values',[EBITDA]<>0))
 
Variance = calculate([EBITDA], filter('values',[EBITDA]<>0),filter('values','values'[Scenario] = "Actual")) - calculate([EBITDA], filter('values',[EBITDA]<>0),filter('values','values'[Scenario] = "Budget"))
 
I am trying to show Actual, Budget, and the Variance (Actual-Budget) in one matrix table visualization. 
I get the below tables when I use Blank Ebitda and Variance:
 
 
 

 

How can I build a table that would show EBITDA as?:

 

 

 

 

 

 

 

Need Help
Tips and Tricks

3 Replies

  • parry2k's avatar
    parry2k
    Super User
    6 years ago

    Anonymous I think it should be something like this, add the following measure and put in matrix visual.

     

    Base Sum = SUM ( Table[Amount] )

Actual = CALCULATE ( [Base Sum], Table[Scenario] = "Actual" )

Budget = CALCULATE ( [Base Sum], Table[Scenario] = "Budget" )

Variance = [Actual] - [Budget]

     

    I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos whoever helped to solve your problem. It is a token of appreciation!