Forum Discussion

adhumal2's avatar
adhumal2
Helper III
6 years ago

Conditional Formatting for negative values with 3 conditions

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 percen...
Need Help
speedramps's avatar
speedramps
Super User
6 years ago

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's avatar
adhumal2
Helper III
6 years ago

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()
)
  • nandukrishnavs's avatar
    nandukrishnavs
    Community Champion
    6 years ago

    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's avatar
      adhumal2
      Helper III
      6 years ago

       

       

       nandukrishnavs 

       

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

      • nandukrishnavs's avatar
        nandukrishnavs
        Community Champion
        6 years ago

        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         🙂

  • speedramps's avatar
    speedramps
    Super User
    6 years ago

    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.