Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
1 year ago
Solved

Conditional Formatting based on value range and category

Hi All,

 

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
  • grazitti_sapna's avatar
    grazitti_sapna
    1 year ago

    Hi Anonymous ,

    you can create a DAX measure that determines the color based on the value and the thresholds defined, Use Below DAX

    Category Color =
    SWITCH(
    TRUE(),
    'Table'[Category] = "Category 1" && 'Table'[Value] >= 895, "Red",
    'Table'[Category] = "Category 1" && 'Table'[Value] <= 649, "Green",
    'Table'[Category] = "Category 1" && 'Table'[Value] > 649 && 'Table'[Value] < 895, "Amber",
    'Table'[Category] = "Category 2" && 'Table'[Value] >= 1000, "Red",
    'Table'[Category] = "Category 2" && 'Table'[Value] <= 705, "Green",
    'Table'[Category] = "Category 2" && 'Table'[Value] > 705 && 'Table'[Value] < 1000, "Amber",
    "Transparent" -- Default value if none of the conditions are met
    )

    Apply Conditional Formatting in the Visual

    1. Select the Bar Chart:

      • In your Power BI report, select the bar chart visualization.

    2. Go to the "Format" Pane:

      • Expand the "Data colors" section.

    3. Click on the "fx" Button:

      • You'll see an option to set conditional formatting. Click the "fx" button next to "Data colors" to apply conditional formatting.

    4. Set the Conditional Formatting Based on the Measure:

      • In the conditional formatting dialog box, select the "Format by" option as "Field value".

      • Choose your Category Color measure for the "Based on field" option.

      • This will ensure that each bar color is based on the color value returned by your Category Color measure.

    🌟 I hope this solution helps you unlock your Power BI potential! If you found it helpful, click 'Mark as Solution' to guide others toward the answers they need.
    💡 Love the effort? Drop the kudos! Your appreciation fuels community spirit and innovation.
    🎖 As a proud SuperUser and Microsoft Partner, we’re here to empower your data journey and the Power BI Community at large.
    🔗 Curious to explore more? [Discover here].
    Let’s keep building smarter solutions together!

3 Replies

  • grazitti_sapna's avatar
    grazitti_sapna
    Super User
    1 year ago

    Hi Anonymous ,

    you can create a DAX measure that determines the color based on the value and the thresholds defined, Use Below DAX

    Category Color =
    SWITCH(
    TRUE(),
    'Table'[Category] = "Category 1" && 'Table'[Value] >= 895, "Red",
    'Table'[Category] = "Category 1" && 'Table'[Value] <= 649, "Green",
    'Table'[Category] = "Category 1" && 'Table'[Value] > 649 && 'Table'[Value] < 895, "Amber",
    'Table'[Category] = "Category 2" && 'Table'[Value] >= 1000, "Red",
    'Table'[Category] = "Category 2" && 'Table'[Value] <= 705, "Green",
    'Table'[Category] = "Category 2" && 'Table'[Value] > 705 && 'Table'[Value] < 1000, "Amber",
    "Transparent" -- Default value if none of the conditions are met
    )

    Apply Conditional Formatting in the Visual

    1. Select the Bar Chart:

      • In your Power BI report, select the bar chart visualization.

    2. Go to the "Format" Pane:

      • Expand the "Data colors" section.

    3. Click on the "fx" Button:

      • You'll see an option to set conditional formatting. Click the "fx" button next to "Data colors" to apply conditional formatting.

    4. Set the Conditional Formatting Based on the Measure:

      • In the conditional formatting dialog box, select the "Format by" option as "Field value".

      • Choose your Category Color measure for the "Based on field" option.

      • This will ensure that each bar color is based on the color value returned by your Category Color measure.

    🌟 I hope this solution helps you unlock your Power BI potential! If you found it helpful, click 'Mark as Solution' to guide others toward the answers they need.
    💡 Love the effort? Drop the kudos! Your appreciation fuels community spirit and innovation.
    🎖 As a proud SuperUser and Microsoft Partner, we’re here to empower your data journey and the Power BI Community at large.
    🔗 Curious to explore more? [Discover here].
    Let’s keep building smarter solutions together!