Conditional formatting based on category and value range

[Anonymous]

Hi All I asked this yesterday but accidentally closed the thread,

I have been asked to create a new set of thresholds for a bar chart so that the color of the bar is based on a value range for each specific category. For Example,

Category 1 Value>=895 then red <=649 then green and between the two Amber

Category 2 Value >=1000 then red <=705 green etc.

What is the best way to go about this?

Thanks in advance

Mike

[mdaatifraza5556 · Accepted solution]

Hi @Anonymous 

Can you please try the steps below to achieve your result?

1. Create a measure.

Bar Color =

SWITCH(

    TRUE(),

    // Category 1

    SELECTEDVALUE('Table'[Category]) = "Category 1" && [total value] >= 895, "#FF0000",  -- Red

    SELECTEDVALUE('Table'[Category]) = "Category 1" && [total value] <= 649, "#00FF00",  -- Green

    SELECTEDVALUE('Table'[Category]) = "Category 1", "#FFC000",                   -- Amber

    // Category 2

    SELECTEDVALUE('Table'[Category]) = "Category 2" && [total value] >= 1000, "#FF0000",

    SELECTEDVALUE('Table'[Category]) = "Category 2" && [total value] <= 705, "#00FF00",

    SELECTEDVALUE('Table'[Category]) = "Category 2", "#FFC000",

    // Default

    "#FFFFFF"  

)

2. Apply the measure in conditional formatting

    a. Click on the bar chart.

   b. In the visualization pane, go to the Format section expand column. 

   c. Clcik the fx button next Default color.

   d. Choose.

       --- Format by Field value

      ----Based on field select the bar color measuer we created.

If this answers your questions, kindly accept it as a solution and give kudos.

[Akash_Varuna]

@Anonymous This could be achieved by conditional formatting in Power BI using a DAX measure like below:

Bar Color = 
SWITCH(
    TRUE(),
    SELECTEDVALUE(Table[Category]) = "Category 1" && Table[Value] >= 895, "Red",
    SELECTEDVALUE(Table[Category]) = "Category 1" && Table[Value] <= 649, "Green",
    "Amber"
)

Apply this measure in the Data Colors section of the chart and repeat the logic for other categories and value ranges.
If this post helped please do give a kudos and accept this as a solution
Thanks In Advance