If formula error

[Anonymous]

Hi expert

I cannot see my error here..

Margin Range = if('Fact_Net_Billing'[Net Margin (%)]<=.1,"<10%",

                    if(AND('Fact_Net_Billing'[Net Margin (%)]>=.10 && 'Fact_Net_Billing'[Net Margin (%)]<=.2,"10-20%"),

                        if(AND('Fact_Net_Billing'[Net Margin (%)]>=.2 && 'Fact_Net_Billing'[Net Margin (%)]<=.4,"20-40%"), ">40%")))

[PC2790 · Accepted solution]

Hi @Anonymous ,

There is no need of applying the AND operation twice by adding 'AND' & '&&'.

Try removing one, something like:

Margin Range =
IF (
    [NetMargin] <= .1,
    "<10%",
    IF (
        
            [NetMargin] >= .10
                && [NetMargin] <= .2,
            "10-20%"
        ,
        IF (
            
                [NetMargin] >= .2
                    && [NetMargin] <= .4,
                "20-40%"
            ,
            ">40%"
        )
    )
)

Also, looking at your requirement, I would suggest using a Switch statement here which would look something like this:

Margin Range =
SWITCH (
    TRUE (),
    [NetMargin] <= 0.1, "<10%",
    AND ( [NetMargin] >= .10, [NetMargin] ) <= .2, "10-20%",
    AND ( [NetMargin] >= .2, [NetMargin] ) <= .4, "20-40%",
    ">40%"
)

I hope this helps

[PhilipTreacy]

Hi @Anonymous 

You'e using both AND() and && when you only need one of these.

Try this instead

Margin Range = if('Fact_Net_Billing'[Net Margin (%)] <= .1, "<10%",

                    if(AND('Fact_Net_Billing'[Net Margin (%)] >= .10 , 'Fact_Net_Billing'[Net Margin (%)] <= .2) ,"10-20%",

                        if(AND('Fact_Net_Billing'[Net Margin (%)] >= .2 , 'Fact_Net_Billing'[Net Margin (%)] <= .4),"20-40%", ">40%")))

Regards

Phil