Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
1 year ago
Solved

Conditional Formatting with % and criteria

Hi There,

 

I am having asn issue while creating conditional formatting.

I have one column which is Service Level (%) and having values in % along with that i have criteria based on LOB'S present in my data.

I am struggling to apply conditional formatting . Can anyone help me in acheving below requirement :

So I need Conditional formatting in Serivce Level columns based on Conditions given .

 



Request to please help me in achieving this .

 

Regards,

Ash

amitchandak Ashish_Mathur 

Need Help
Show and Tell
Tips and Tricks
Tutorial Requests
  • SamWiseOwl's avatar
    SamWiseOwl
    1 year ago

    Hi Anonymous 

    Create a measure in your table and use that for formatting:

    Colour measure =

    var ser = sum('Table'[Service level]) --if this is a measure just put the measure name

    var lob = selectedValue('Table'[LOBs])

    RETURN
    Switch(
    True()
    , ser > 0.7 || ser > 0.55 && lob IN {"A","B"} || lob = "D" && ser > 60

    ,"Green"

    , ser > 0.6 || ser > 0.50 && lob IN {"A","B"} || lob = "D" && ser > 50

    ,"Yellow"

    , ser > 0.5 || ser > 0.45 && lob IN {"A","B"} || lob = "D" && ser > 40

    ,"Red"

    ,"Blue")

    Then assign this to your field formatting:

     

  • SamWiseOwl's avatar
    SamWiseOwl
    1 year ago

    (lob = "D" || si in {"d(I)",d(ii)})  && ser > 60

     

    Need the brackets so they happen at the same time.

    Assuming its Lob = D OR Si = "d(i)" if it is AND use &&

8 Replies

  • SamWiseOwl's avatar
    SamWiseOwl
    Super User
    1 year ago

    Hi Anonymous 

    Create a measure in your table and use that for formatting:

    Colour measure =

    var ser = sum('Table'[Service level]) --if this is a measure just put the measure name

    var lob = selectedValue('Table'[LOBs])

    RETURN
    Switch(
    True()
    , ser > 0.7 || ser > 0.55 && lob IN {"A","B"} || lob = "D" && ser > 60

    ,"Green"

    , ser > 0.6 || ser > 0.50 && lob IN {"A","B"} || lob = "D" && ser > 50

    ,"Yellow"

    , ser > 0.5 || ser > 0.45 && lob IN {"A","B"} || lob = "D" && ser > 40

    ,"Red"

    ,"Blue")

    Then assign this to your field formatting:

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      1 year ago

      Hi SamWiseOwl ,

       

      That was really quick. Thank you for this . I appreicate it .

       

      Thanks,

      Ashish

    • Anonymous's avatar
      Anonymous
      Not applicable
      1 year ago

      Hi SamWiseOwl 

      I also need to add two more conditions which is in different columns .
      Can you tell what should i modify in this?

      Thanks,

      Ashish




       

      • SamWiseOwl's avatar
        SamWiseOwl
        Super User
        1 year ago
        Colour measure =

         

        var ser = sum('Table'[Service level]) --if this is a measure just put the measure name

         

        var lob = selectedValue('Table'[LOBs])
         
        var si = selectedValue('Table'[Si's])

         

        RETURN
        Switch(
        True()
        , ser > 0.7 || ser > 0.55 && (lob IN {"A","B"} || si IN {"a(i)","b(i)"})  || lob = "D" && ser > 60

         

        ,"Green"

         

        , ser > 0.6 || ser > 0.50 && (lob IN {"A","B"} || si IN {"a(i)","b(i)"})  || lob = "D" && ser > 50

         

        ,"Yellow"

         

        , ser > 0.5 || ser > 0.45 && (lob IN {"A","B"} || si IN {"a(i)","b(i)"})  || lob = "D" && ser > 40

         

        ,"Red"

         

        ,"Blue")
  • dk_dk's avatar
    dk_dk
    Super User
    1 year ago

    Hi Anonymous ,

    You could do this by creating a new calculated column that captures your criteria ( using SWITCH and IF statements) and returns the corresponding hex color code. Then you can use this calculated column to drive the conditional formatting of the service level column.

    I hope this helps!