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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Anonymous
Not applicable

Variables Dax

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

2 REPLIES 2
v-cgao-msft
Community Support
Community Support

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

amitchandak
Super User
Super User

@Anonymous , Try Result like

 

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

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

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

Top Solution Authors
Top Kudoed Authors