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

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
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.

Top Solution Authors