Forum Discussion
Dax if/then divide statement
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,
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 )
8 Replies
Are Variance and Budgets columns or measures?
Hello FreemanZ, Variance and Budgets are measures.
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 ) ) )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.
Using the very same example, what should be the vslue of the total?
