Variables Dax

Anonymous — Mon, 04 Jul 2022 05:12:37 GMT

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

Re: Variables Dax

amitchandak — Mon, 04 Jul 2022 07:11:37 GMT

Anonymous , Try Result like

VAR Result = Sumx(values( 'P&L Template'[Lookup Column] ), Actuals - Budget)

Re: Variables Dax

Anonymous — Wed, 27 Jul 2022 07:03:08 GMT

Hi Anonymous ,

Has this question been solved? If amitchandak's answer is helpful, please consider marking his answer as a solution, which will help more people find the answer faster. If the problem is not resolved, please consider publishing some sample data. Thanks in advance!

Best Regards,
Gao
Community Support Team

If there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly -- How to provide sample data

topic Re: Variables Dax in DAX Commands and Tips

Variables Dax

Re: Variables Dax

Re: Variables Dax