Conditional Formatting for negative values with 3 conditions

[adhumal2]

Hi Experts,

I want to do the conditional formatting of negative values as mentioned below. How can I achieve it with icons.

also , please please let me know how to work with numbers or percentages while doing the conditional formatting

Condition	Icon
Greater than or equal to -100 but less than or equal to -5	Green Icon
Greater than -5 but leass than or equal to 0	Yello icon
Greater than 0 but less than or equal to 100	Red Icon

[dax]

Hi @adhumal2 , 

You could refer to @speedramps 's suggestions, or set conditional formatting like below

Best Regards,
Zoe Zhi

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

[speedramps]

Hi adhumal

Please conisder this solution

Create this measure and drag and drop it to your visual.

the UNICHAR will create the icon for you.

Myicon =

VAR Myvalue = -4     -- substitute this hard code value with you measure and multiply with 100 if a %

RETURN

SWITCH(TRUE(),

Myvalue < -100, BLANK(),

Myvalue <= -5, UNICHAR(128994), -- green if > -100 and <= -5

Myvalue <= -0, UNICHAR(128993), -- yellow if > -5 and <= 0

Myvalue <= 100 , UNICHAR(128308), -- red if >0 and <=100

BLANK()

)

[adhumal2]

@speedrampsI tweaked the formula, but it still gives me the same result. Sorry but I am quite new to this

Here is the DAX that i used

Myicon2 =

            VAR MYVALUE = [Difference %] * 100

            RETURN 

            SWITCH(TRUE(),

            MYVALUE < -100, BLANK(),

            MYVALUE <= -5, UNICHAR(128994),

            MYVALUE <= -0, UNICHAR(128993),

            MYVALUE <= 100, UNICHAR(128308),

            BLANK()

            )

[adhumal2]

@speedramps  Thank you. I added the measure provided by you but it only gives me the 'red' icons only. Below is the screenshot

Here is the DAX 

MyICON =

        VAR MYVALUE = [Difference %]

        RETURN

        SWITCH(TRUE(),

        MYVALUE<-100,BLANK(),

        Myvalue <= -5, UNICHAR(128994), -- green if > -100 and <= -5

        Myvalue <= -0, UNICHAR(128993), -- yellow if > -5 and <= 0

        Myvalue <= 100 , UNICHAR(128308), -- red if >0 and <=100

        BLANK()

)

[speedramps]

Hi again adhumal

In my instructions it did say ...
VAR Myvalue = -4 -- substitute this hard code value with your measure and multiply with 100 if a %

but unfortunately you have done this
VAR MYVALUE = [Difference %]

[Difference %] has a percent datatype so 13% will be stored as 0.13

and therefore 0.13 is less than 5

If you tweak it will work.

[nandukrishnavs]

@adhumal2 

Try this 

MyICON =
VAR MYVALUE = [Difference %]
VAR RESULT =
    IF (
        MYVALUE > -1
            && MYVALUE <= -0.05,
        UNICHAR ( 128994 ),
        IF (
            MYVALUE > -0.05
                && MYVALUE <= 0,
            UNICHAR ( 128993 ),
            IF (
                MYVALUE > 0
                    && MYVALUE <= 100,
                UNICHAR ( 128308 ),
                BLANK ()
            )
        )
    )
RETURN
    RESULT

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

[adhumal2]

 @nandukrishnavs 

Thanks for your reply. I used your formula but its showing me below results still (only red icons appear)

[nandukrishnavs]

@adhumal2 

Follow this.

Create a DAX measure for an icon.

Icon = UNICHAR(11044)

Use this measure in your table visual.

Create another measure for conditional formatting.

Color = 
VAR MYVALUE = [Difference %]
VAR RESULT =
    IF (
        MYVALUE > -1
            && MYVALUE <= -0.05,
        "green",
        IF (
            MYVALUE > -0.05
                && MYVALUE <= 0,
            "orange",
            IF (
                MYVALUE > 0
                    && MYVALUE <= 100,
                "red",
                BLANK ()
            )
        )
    )
RETURN
    RESULT

Then format the Icon (Font) measure field using the newly created "Color" measure.

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

[adhumal2]

@nandukrishnavs- Superb, That worked perfectly. Many Thanks.

[nandukrishnavs]

@adhumal2 

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

[nandukrishnavs]

@adhumal2 

Refer to this https://exceleratorbi.com.au/conditional-formatting-using-icons-in-power-bi/

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