Formula multiples requirements return color Status

[romovaro]

Hello

I have the table below:

What I am trying to achieve is a formula where I can get all the requirements below and return colors:

RED

AMBER

GREEN

No countries under Imp

Criteria:

Unscheduled % up to 10% is GREEN/, >10% is AMBER and >25% RED 

HNS % up to 10% is GREEN/, >10% is AMBER and >25% RED 

Amber-Red up to 15% is GREEN, >15% is AMBER , >20% RED

And if countries_UI is blank, then No countries under Imp

ANy help is welcome

Thanks

[muhammad_786_1]

Hi @romovaro 

You can use this measure to get your desired output:

Color_Status = 
VAR UnscheduledStatus = 
    SWITCH(
        TRUE(),
        [Unscheduled%] <= 0.10, "Green",
        [Unscheduled%] > 0.10 && [Unscheduled%] <= 0.25, "Amber",
        [Unscheduled%] > 0.25, "Red"
    )

VAR HNSStatus = 
    SWITCH(
        TRUE(),
        [HNS%] <= 0.10, "Green",
        [HNS%] > 0.10 && [HNS%] <= 0.25, "Amber",
        [HNS%] > 0.25, "Red"
    )

VAR AmberRedStatus = 
    SWITCH(
        TRUE(),
        [Amber-Red] <= 0.15, "Green",
        [Amber-Red] > 0.15 && [Amber-Red] <= 0.20, "Amber",
        [Amber-Red] > 0.20, "Red"
    )

VAR FinalColor = 
    IF(
        ISBLANK(SELECTEDVALUE('Table'[Countries_UI])), 
        "No countries under Imp",
        IF(
            UnscheduledStatus = "Red" || HNSStatus = "Red" || AmberRedStatus = "Red", "Red",
            IF(
                UnscheduledStatus = "Amber" || HNSStatus = "Amber" || AmberRedStatus = "Amber", "Amber",
                "Green"
            )
        )
    )

RETURN FinalColor

Output:

Best Regards,

Muhammad Yousaf

If this post helps, then please consider "Accept it as the solution" to help the other members find it more quickly.

LinkedIn

[romovaro]

HI @muhammad_786_1 

Thanks. Looks good. I am having an issue with one of the measures:

Tried using selectedmeasure but not working.

I also created a column showing 0, 1 and tried to adapt to your formula but again...the last part is showing isses.

UI Countries Count = IF('PV Cel File'[Status]="under Implementation", "1", "0")

Countries UI =

CALCULATE(

    SUM('PV Cel File'[UI Countries Count]),

    ALLEXCEPT('PV Cel File','PV Cel File'[CID])

)

Could you help me, pls?

[muhammad_786_1 · Accepted solution]

I thought Countries_UI was not a measure. But, it is also a measure then you can just replace that line with this line:

ISBLANK([Countries_UI]),

Best Regards,
Muhammad Yousaf

If this post helps, please consider "Accept it as the solution" to help the other members find it more quickly.

LinkedIn

[ToddChitt]

@romovaro Were you looking for the word "Green", or formatting the actual cell with a green background? Please be specific about what you desire. You showed us nice input data. Can you show us sample desired results?

[ToddChitt]

Hello @romovaro 

That is fine. The Conditional Formatting logic is applied at the aggregation level (either a numeric field that is aggregated in the visual, or a measure) of the visual:

[ToddChitt]

This sounds like a simple job for Conditional Formatting (Right-click on a field in the visual, and select Conditional Formatting. You get these options:

[romovaro]

hi Todd

Thanks for your answer. THe thing is that all the fields are measure.

data source is showing multiples rows for every customer and the table is summarizing all the data in one row x customer.