Conditional Formatting based on value range and category

[Anonymous]

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

[grazitti_sapna · Accepted solution]

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!

[Anonymous]

Hi @grazitti_sapna 
I tried this however it returns an error on the category table as there is more than one value

[Uzi2019]

Hi @Anonymous 

Check this videos for better understanding

https://www.youtube.com/shorts/B0SAKbRrVWo

https://www.youtube.com/watch?v=MItQGef79Lc

https://www.youtube.com/watch?v=p6hqBEJTlRc

I hope above videos would helpyou to solve your query with little modification in your formula..

I hope I answered your question!