The ultimate Fabric, Power BI, SQL, and AI community-led learning event. Save €200 with code FABCOMM.
Get registeredCompete to become Power BI Data Viz World Champion! First round ends August 18th. Get started.
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,
Solved! Go to 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
)
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?
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% |
@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
)
Thank you so much for all your help. Happy holidays.
Are Variance and Budgets columns or measures?
Hello FreemanZ, Variance and Budgets are measures.
User | Count |
---|---|
28 | |
12 | |
8 | |
7 | |
5 |
User | Count |
---|---|
36 | |
14 | |
12 | |
7 | |
7 |