Switch with multiple conditions in a variance

[Anonymous]

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!

variance Mod =

var Forecast = 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

Thanks,

SV

[Fowmy · Accepted solution]

@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

[Syndicate_Admin]

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

[Anonymous]

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

[smpa01]

@Anonymous  can you reverse the switch order and try

SWITCH(TRUE(),
VarianceR<-5,VarianceR+5,
VarianceR>=-5&&VarianceR<=0,0,
VarianceR>0,VarianceR,)

[mattww]

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?