Dax if/then divide statement

[mariomyhanh]

Please help, i'm fairly new to DAX and I am trying create a measure to look at a column ([Level 2]) in a table ('COA Structure') that contains "Total Operating Expense" then apply this division formula to it  = DIVIDE([Variance],[Budgets]*-1,-1).  but if column has "Total Operating Revenue" then apply this division formula to it= DIVIDE([Variance],[Budgets]*1,1). i hope this makes sense. 

Thank you,

[tamerj1]

Hi @mariomyhanh 

Please try

=
SUMX (
    VALUES ( 'COA Structure'[Level 2] ),
    SWITCH (
        'COA Structure'[Level 2],
        "Total Operating Expense", DIVIDE ( [Variance], [Budgets] * -1, -1 ),
        "Total Operating Revenue", DIVIDE ( [Variance], [Budgets] * 1, 1 )
    )
)

[mariomyhanh]

Thank you so much for the formula!!  It works.  Is there a way to customize the total . Currently, if revenue is -3.19 and expenses is 2.51. Total is -0.68. is there a way to customize the formula for row subtotal to calculate variance/budget ?

Thank you again so much.

[tamerj1]

@mariomyhanh 

Using the very same example, what should be the vslue of the total?

[mariomyhanh]

HI Tamerj1 total should be 2.5%.  but currently it's sum it to -0.68%

	Budget	Variance	%Variance
revnue	3,600,000	(116,000)	-3.2%
expense	147,000,000	3,700,000	2.5%
total	(143,400,000)	3,584,000	-2.5%

[tamerj1 · Accepted solution]

@mariomyhanh 
Please try

=
VAR Result =
    DIVIDE ( [Variance], [Budgets] )
RETURN
    SWITCH (
        SELECTEDVALUE ( 'COA Structure'[Level 2] ),
        "Total Operating Expense", IFERROR ( - Result, -1 ),
        "Total Operating Revenue", IFERROR ( Result, 1 ),
        Result
    )

[mariomyhanh]

Thank you so much for all your help. Happy holidays. 

[FreemanZ]

Are Variance and Budgets columns or measures?

[mariomyhanh]

Hello FreemanZ, Variance and Budgets are measures.