Hi,
I have the following VAR DAX which is trying to work out the variance between Actuals and Budget.
There are 3 VAR :
Var Actual, Budget, Result. When I return Actual and budget seperately they work but I am unable to go Result = Actual - Budget?
Is anyone can assist would be great. Thanks in advance.
Variances =
VAR LineItem =
SELECTEDVALUE ( 'P&L Template'[Lookup Column] )
VAR Actuals = SWITCH ( TRUE (),
LineItem = "Revenue - Secured", DIVIDE ( 'Key measures'[Revenue - Secured], 1000, 0 ),
LineItem = "COGS - Secured", DIVIDE ( 'Key measures'[COGS - Secured], 1000, 0 ),
LineItem = "Gross Margin - Secured", DIVIDE ( 'Key measures'[Gross Margin - Secured], 1000, 0 ),
LineItem = "Revenue - Pipeline", DIVIDE ( 'Key measures'[Revenue - Pipeline], 1000, 0 ),
LineItem = "COGS - Pipeline", DIVIDE ( 'Key measures'[COGS - Pipeline], 1000, 0 ),
LineItem = "Gross Margin - Pipeline", DIVIDE ( 'Key measures'[Gross Margin - Pipeline], 1000, 0 ),
LineItem = "Gross Margin - Blue Sky", DIVIDE ( 'Key measures'[Gross Margin - Blue Sky], 1000, 0 ),
LineItem = "COGS - Blue Sky", DIVIDE ( 'Key measures'[COGS - Blue Sky], 1000, 0 ),
LineItem = "Total Revenue", DIVIDE ( 'Key measures'[Actual Total Revenue], 1000, 0 ),
LineItem = "Total COGS", DIVIDE ( 'Key measures'[Total COGS], 1000, 0 ),
LineItem = "Total Gross Margin", DIVIDE ( 'Key measures'[Total Gross Margin], 1000, 0 ),
LineItem = "Other Revenue", DIVIDE ( 'Key measures'[Other Revenue], 1000, 0 ),
LineItem = "Total Cost Of Goods Sold", DIVIDE ( 'Key measures'[Total Cost of Goods Sold], 1000, 0 ),
LineItem = "Gross Margin from Trading", DIVIDE ( 'Key measures'[Gross Margin from Trading], 1000, 0 ),
LineItem = "Blue Collar Labour Costs", DIVIDE ( 'Key measures'[Blue Collar Labour Costs], 1000, 0 ),
LineItem = "Total Direct Costs", DIVIDE ( 'Key measures'[Total Direct Costs], 1000, 0 ),
LineItem = "Monthly Salary Costs", DIVIDE ( 'Key measures'[Monthly Salary Costs], 1000, 0 ),
LineItem = "Indirect Variable Costs", DIVIDE ( 'Key measures'[Indirect Variable Costs], 1000, 0 ),
LineItem = "Indirect Fixed Costs", DIVIDE ( 'Key measures'[Indirect Fixed Costs], 1000, 0 ),
LineItem = "Total Recharges", DIVIDE ( 'Key measures'[Total Recharges], 1000, 0 ),
LineItem = "Total Overhead Cost", DIVIDE ( 'Key measures'[Total Overhead Costs], 1000, 0 ),
LineItem = "(Under)/ Over Recoveries", DIVIDE ( 'Key measures'[(Under) / Over Recoveries], 1000, 0 ),
LineItem = "EBIT", DIVIDE ( 'Key measures'[Actual EBIT], 1000, 0 ),
LineItem = "", 'Key measures'[Blank],
CALCULATE (
DIVIDE ( [Fin Actuals], 1000, 0 ),
FILTER ( 'P&LData', 'P&LData'[Category DP] = LineItem )
)
)
VAR Budget = SWITCH ( TRUE (),
LineItem = "Gross Margin - Secured", DIVIDE ( 'Key measures'[B GM - Secured], 1000 ),
LineItem = "Gross Margin - Pipeline", DIVIDE ( 'Key measures'[B GM - Pipeline], 1000 ),
LineItem = "Gross Margin - Blue Sky", DIVIDE ( 'Key measures'[B GM - BlueSkye], 1000 ),
LineItem = "Total Revenue", DIVIDE ( 'Key measures'[B Total Revenue], 1000 ),
LineItem = "Total COGS", DIVIDE ( 'Key measures'[B Total COGS], 1000 ),
LineItem = "Total Gross Margin", DIVIDE ( 'Key measures'[B Total GM], 1000 ),
LineItem = "Other Revenue", DIVIDE ( 'Key measures'[B Other Revenue], 1000 ),
LineItem = "Total Cost Of Goods Sold", DIVIDE ( 'Key measures'[B Total Cost Of Goods Sold], 1000 ),
LineItem = "Gross Margin from Trading", DIVIDE ( 'Key measures'[B GM from Trading], 1000 ),
LineItem = "Blue Collar Labour Costs", DIVIDE ( 'Key measures'[B Blue Collar Labour Costs], 1000 ),
LineItem = "Total Direct Costs", DIVIDE ( 'Key measures'[B Total Direct Costs], 1000 ),
LineItem = "Monthly Salary Costs", DIVIDE ( 'Key measures'[B Monthly Salary Costs], 1000 ),
LineItem = "Indirect Variable Costs", DIVIDE ( 'Key measures'[B Indirect Variable Costs], 1000 ),
LineItem = "Indirect Fixed Costs", DIVIDE ( 'Key measures'[B Indirect Fixed Costs], 1000 ),
LineItem = "Total Recharges", DIVIDE ( 'Key measures'[B Total Recharges], 1000 ),
LineItem = "Total Overhead Cost", DIVIDE ( 'Key measures'[B Total Overhead Cost], 1000 ),
LineItem = "(Under)/ Over Recoveries", DIVIDE ( 'Key measures'[B (Under) Over Recoveries], 1000 ),
LineItem = "EBIT", DIVIDE ( 'Key measures'[Budget EBIT], 1000 ),
LineItem = "", 'Key measures'[Blank],
CALCULATE (
DIVIDE ( [B Amount], 1000 ),
FILTER ( 'Budgets', 'Budgets'[Lookup Line Item] = LineItem )
)
)
VAR Result = Actuals - Budget
RETURN
Result
