Forum Discussion
Switch with multiple conditions in a variance
Hi All ,
I am trying to create a variance of shipment from budgeted. However , the customer allows permissible variance which I am trying to build in DAX function. the below function is returning zero for all. Here are the conditions I am trying to build.
| If Variance (shipment-budgeted) is > 0 ; then report the actual varaiance | Eg : Variance = 15 ; result =15 |
| If Variance is less than 0 but greater than -5 , then report vairance as 0 | Eg : Variance = - 4; result = 0 |
| If variance is less than -5 , then add 5 to the actual variance | Eg : Variance = -25 , result = -20 |
Here is the function I am using . I think >=0 <= -5 condition is incorrect , but really dont know how to write. really appreciate any help!
Anonymous
Can you the values in the columns and the filter context on which you are running this measure?
Try the following:varianceMod = VAR Forecast =CALCULATE ( SUM ( 'Monthly Budget'[Order Budget] ) ) VAR VarianceR = [Shipment_tons] - Forecast VAR switchFnc = SWITCH ( TRUE (), VarianceR > 0, VarianceR, VarianceR >= -5 && VarianceR <= 0, 0, VarianceR < -5, VarianceR + 5 ) RETURN switchFnc
5 Replies
Anonymous
Can you the values in the columns and the filter context on which you are running this measure?
Try the following:varianceMod = VAR Forecast =CALCULATE ( SUM ( 'Monthly Budget'[Order Budget] ) ) VAR VarianceR = [Shipment_tons] - Forecast VAR switchFnc = SWITCH ( TRUE (), VarianceR > 0, VarianceR, VarianceR >= -5 && VarianceR <= 0, 0, VarianceR < -5, VarianceR + 5 ) RETURN switchFncWhat could be the reason , when the above variance is put in a matrix the row total gives incorrect results (Budget and shipmet totals are correct) ? I checked all the filters , but cannot figure out the reason.
Hi Anonymous ,
That expression seems to be working on my end when I've built it in a simple model, could it be something with your relationships that's causing the issue?
Anonymous can you reverse the switch order and try
SWITCH(TRUE(), VarianceR<-5,VarianceR+5, VarianceR>=-5&&VarianceR<=0,0, VarianceR>0,VarianceR,)
