Need to create a column with groupings based upon another column's values

[fusion1966]

I'm relatively new to Power BI so some advice would be great.

I have a calculated column which works out the number of days as of today that a request has been with my team.

What I want to do is to categorise the number of days values into groups:

Green = less that 120 days

Yellow = 120-149 days

Orange = 150-180 days

Red = over 180 days

The green and red groupings are easy enough and I have written the following formula:

days taken group = IF('Finance Instructions Received'[Days Ongoing]>180, "Red", IF('Finance Instructions Received'[Days Ongoing]<120, "Green"))

However I am struggling to include the yellow and orange groups into the formula.

Can anyone show me how this formula should be written please?

[amitchandak · Accepted solution]

@fusion1966 , Try like

Switch( true(),
'Finance Instructions Received'[Days Ongoing] < 120 , "Green",
'Finance Instructions Received'[Days Ongoing] < 150 , "Yellow",
'Finance Instructions Received'[Days Ongoing] < 180 , "Orange",
"Red")

[v-jayw-msft]

Hi @fusion1966 ,

Check

Column =
IF (
    'Finance Instructions Received'[Days Ongoing] < 120,
    "Green",
    IF (
        'Finance Instructions Received'[Days Ongoing] >= 120
            && 'Finance Instructions Received'[Days Ongoing] < 150,
        "Yellow",
        IF (
            'Finance Instructions Received'[Days Ongoing] >= 150
                && 'Finance Instructions Received'[Days Ongoing] <= 180,
            "Orange",
            "Red"
        )
    )
)

Best Regards,

Jay

[fusion1966]

thanks all, that worked perfectly!

[parry2k · Accepted solution]

@fusion1966 add your calculation like this

SWTICH ( TRUE(),
Table[Column] > 180, "Red",
Table[Column] > 150, "Orange",
Table[Column] > 120, "Yellow",
"Green"
)

I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos whoever helped to solve your problem. It is a token of appreciation!

⚡Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡

[amitchandak · Accepted solution]

@fusion1966 , Try like

Switch( true(),
'Finance Instructions Received'[Days Ongoing] < 120 , "Green",
'Finance Instructions Received'[Days Ongoing] < 150 , "Yellow",
'Finance Instructions Received'[Days Ongoing] < 180 , "Orange",
"Red")