Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

If formula error

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%")))

 

Need Help
  • PC2790's avatar
    PC2790
    5 years ago

    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

2 Replies

  • PhilipTreacy's avatar
    PhilipTreacy
    Super User
    5 years ago

    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

  • PC2790's avatar
    PC2790
    Community Champion
    5 years ago

    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