Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
1 year ago
Solved

Conditional formatting based on category and value range

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

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

    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.

2 Replies

  • mdaatifraza5556's avatar
    mdaatifraza5556
    Super User
    1 year ago

    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's avatar
    Akash_Varuna
    Super User
    1 year ago

    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