IF formula with another if condition does not work properly

[Anonymous]

Hello Community,

I am trying to categorize the sales by using IF formula

here is the DAX code ;

Sales category = IF(Orders[Sales] < 100,

                                     "Low",

                               IF(Orders[Sales]<1000,

                                 "Medium",

                                 "Hight"

                                  )

                              )

however, the result is not correct. 

Anyone can help to explain why and provide some solution? 

Thank you so much

[harshnathani]

Hi @Anonymous ,

You can try this measure.

Total Sales = Sum(Order[Sales])

Sales Category =
SWITCH (
    TRUE (),
    [Total Sales] < 100, "Low",
     [Total Sales] >= 100
        &&  [Total Sales] < 1000, "Medium",
    "High"
)

Regards,

Harsh Nathani

Did I answer your question? Mark my post as a solution! Appreciate with a Kudos!!

[mahoneypat · Accepted solution]

Are you writing a calculation column or measure expression?  What you've written would work in a column, but should return an error as a measure.  Your pic looks like a table visual, so you would need to write a measure differently.  Two suggestions:

1. Write it using the SWITCH(TRUE(), ... approach.  See this article - https://powerpivotpro.com/2015/03/the-diabolical-genius-of-switch-true/ .  It's a better/easier way to write nested IFs.

2. First calculate your result as a variable, then use it in SWITCH()

NewMeasure = var totalsales = SUM(Orders[Sales])

return SWITCH(TRUE(), totalsales < 100, "Low", totalsales < 1000, "Medium", "High")

If this solution works for you, please mark it as the solution.  Kudos are appreciated too.  Please let me know if not.

Regards,

Pat

[Anonymous]

I wrote it as a new column. Thank you  @mahoneypat  the switch measure is working perfectly.

What I don't understand is why the nested if calculated column does not work properly. Did I miss something?

Thank you