Forum Discussion

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>=89...

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

Select the Bar Chart:

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

Go to the "Format" Pane:

Expand the "Data colors" section.

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.

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!

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

Select the Bar Chart:
- In your Power BI report, select the bar chart visualization.
Go to the "Format" Pane:
- Expand the "Data colors" section.
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.
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!