Forum Discussion
conditional formatting
I want to apply conditional formatting based of column value with range. e.g. in Central if value between 1 to 10000 then red and 10000 to 15000 then green, in East value between 1 to 2000 then red and 2000 to 5000 with yellow and >5000 then green. this require because some time new state launch and criteria are diffrent please guide how to do the same.
I can see that your data is in long format and you have three columns Product, Zone and Amount. And you used Matrix visual.
Now you can directly create a new calculated column because calculated columns evaluate row by row . In your case, the calculated column can evaluate the Zone and Amount values for each row directly.
You can use the below DAX for new columnZoneColor =SWITCH(TRUE(),'Table'[Zone] = "Central" && 'Table'[Amount] >= 1 && 'Table'[Amount] <= 4000, "#FF0000", // Red for Central, 1 - 4000'Table'[Zone] = "Central" && 'Table'[Amount] > 4000 && 'Table'[Amount] <= 10000, "#008000", // Green for Central, 4000 - 10000'Table'[Zone] = "East" && 'Table'[Amount] >= 1 && 'Table'[Amount] <= 500, "#FF0000", // Red for East, 1 - 500'Table'[Zone] = "East" && 'Table'[Amount] > 500, "#008000", // Green for East, > 500'Table'[Zone] = "North" && 'Table'[Amount] >= 1 && 'Table'[Amount] <= 10000, "#FF0000", // Red for North, 1 - 10000'Table'[Zone] = "North" && 'Table'[Amount] > 10000, "#008000", // Green for North, > 10000'Table'[Zone] = "South" && 'Table'[Amount] >= 1 && 'Table'[Amount] <= 5000, "#FF0000", // Red for South, 1 - 5000'Table'[Zone] = "South" && 'Table'[Amount] > 5000, "#008000", // Green for South, > 5000'Table'[Zone] = "West" && 'Table'[Amount] >= 1 && 'Table'[Amount] <= 20000, "#FF0000", // Red for West, 1 - 20000'Table'[Zone] = "West" && 'Table'[Amount] > 20000, "#008000", // Green for West, > 20000"#FFFFFF" // Default color (white) if no condition is met)
To create a new column click on three dots of the table in the Data pane. You can customize colors as you wish in the DAX query.
Then apply conditional formatting for Amount under the Values dropdown in the visualizations pane.
Choose Field value and newly created column in conditional formatting as shown below.
Then the result looks like this
Please mark this as solution and give kudos if it met your goal.
Thanks,
Vinay.
14 Replies
ReportStatus = SWITCH( TRUE(), 'YourTable'[Region] = "Central" && 'YourTable'[Value] >= 1 && 'YourTable'[Value] <= 10000, "Red", 'YourTable'[Region] = "Central" && 'YourTable'[Value] > 10000 && 'YourTable'[Value] <= 15000, "Green", 'YourTable'[Region] = "East" && 'YourTable'[Value] >= 1 && 'YourTable'[Value] <= 2000, "Red", 'YourTable'[Region] = "East" && 'YourTable'[Value] > 2000 && 'YourTable'[Value] <= 5000, "Yellow", 'YourTable'[Region] = "East" && 'YourTable'[Value] > 5000, "Green", "No Color" )Go to your model, create a new measure, and paste the DAX code above, replacing 'YourTable' with the actual name of your table.
Click on the visual you want to format.
In the Visualizations pane, select the field to format, click the drop-down, and select Conditional formatting.
Select either Background color or Font color.
In the conditional formatting window, choose Field value and select the measure you just created.
Specify the colors for "Red", "Green", and "Yellow" as needed.
Now your visual will reflect the color based on the categorization defined in the measure! Let me know if you need more help!
Create a Measure for Conditional Formatting
ConditionalColor =
SWITCH(
TRUE(),
SELECTEDVALUE('YourTable'[Region]) = "Central" && 'YourTable'[Value] >= 1 && 'YourTable'[Value] <= 10000, "Red",
SELECTEDVALUE('YourTable'[Region]) = "Central" && 'YourTable'[Value] > 10000 && 'YourTable'[Value] <= 15000, "Green",
SELECTEDVALUE('YourTable'[Region]) = "East" && 'YourTable'[Value] >= 1 && 'YourTable'[Value] <= 2000, "Red",
SELECTEDVALUE('YourTable'[Region]) = "East" && 'YourTable'[Value] > 2000 && 'YourTable'[Value] <= 5000, "Yellow",
SELECTEDVALUE('YourTable'[Region]) = "East" && 'YourTable'[Value] > 5000, "Green",
"No Color" // Fallback
)Select the Visual, Go to Format Pane, Click on the fx button next to Conditional formatting, Choose your ConditionalColor measure in the Based on field dropdown
💌 If this helped, a Kudos 👍 or Solution mark would be great! 🎉
Cheers,
Kedar
Connect on LinkedInThanks for your reply , i have below condition
Product South East West North
Prod 1 5000 200 9000 11000
Prod 2 2000 900 3000 1000
Prod 3 1000 0 500 15000
Hi dhrupal_shah,
Take a look at this post: https://community.fabric.microsoft.com/t5/Desktop/Conditional-formatting/m-p/4238800
You could do this using Rules or using a Switch function as you can see in the article.Thanks for your reply , i have below condition
Product South East West North
Prod 1 5000 200 9000 11000
Prod 2 2000 900 3000 1000
Prod 3 1000 0 500 15000
Hi dhrupal_shah
To apply conditional formatting based on specific ranges for different states in Power BI, you can use a DAX measure that defines the color based on your rules. Then, you can apply conditional formatting based on this measure.
Step 1: Create a Measure for Color Formatting
Go to Modeling > New Measure and create a measure to define the color logic:FormatColor = SWITCH( TRUE(), 'Table'[State] = "Central" && 'Table'[Value] >= 1 && 'Table'[Value] <= 10000, "#FF0000", // Red for Central, 1 - 10000 'Table'[State] = "Central" && 'Table'[Value] > 10000 && 'Table'[Value] <= 15000, "#008000", // Green for Central, 10000 - 15000 'Table'[State] = "East" && 'Table'[Value] >= 1 && 'Table'[Value] <= 2000, "#FF0000", // Red for East, 1 - 2000 'Table'[State] = "East" && 'Table'[Value] > 2000 && 'Table'[Value] <= 5000, "#FFFF00", // Yellow for East, 2000 - 5000 'Table'[State] = "East" && 'Table'[Value] > 5000, "#008000", // Green for East, > 5000 "#FFFFFF" // Default to white if none of the conditions are met )Step 2: Apply Conditional Formatting in Power BI
> Select your table or matrix visual.
> Go to the Format your visual pane, expand Conditional formatting, and choose Background color or Font color based on preference.
> Under Format by, select Field value.
> In the Based on field dropdown, select the FormatColor measure.
If new states with different ranges are added, you can update the FormatColor measure by adding additional conditions.Thanks for your reply , i have below condition
Product South East West North
Prod 1 5000 200 9000 11000
Prod 2 2000 900 3000 1000
Prod 3 1000 0 500 15000
Hi, dhrupal_shah
Based on your information, I create a sample table:
Then create a calculated column, try the following dax:
ColorCategory = SWITCH( TRUE(), [Region] = "Central" && [Value] >= 1 && [Value] <= 10000, "Red", [Region] = "Central" && [Value] > 10000 && [Value] <= 15000, "Green", [Region] = "Central" && [Value] > 15000, "Blue", [Region] = "East" && [Value] >= 1 && [Value] <= 2000, "Red", [Region] = "East" && [Value] > 2000 && [Value] <= 5000, "Yellow", [Region] = "East" && [Value] > 5000, "Green", "No Color" )Create a table visual and put Region and Value in it. Click Format pane and expend cell elements, you can choose Background color or Font color.
Select Field value, and then select the calculated column you just created
Here is my preview:
You can learn more from the following link:
Apply conditional table formatting in Power BI - Power BI | Microsoft Learn
How to Get Your Question Answered Quickly
Best Regards
Yongkang Hua
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Thanks for your replay but i have below condition.
Product Central East West South North
Prod1 1000 200 5000 3000 10000
Prod2 2000 500 3000 5000 8000
Hi, dhrupal_shah
To achieve conditional formatting for your table you need to create separate measures for each color such as,Central Color =SWITCH(TRUE(),'Table (2)'[Central] >= 1 && 'Table (2)'[Central] <= 10000, "#FF0000", // Red for Central, 1 - 10000'Table (2)'[Central] > 10000 && 'Table (2)'[Central] <= 15000, "#008000", // Green for Central, 10000 - 15000"#FFFFFF" // Default to white if none of the conditions are met)East Color =SWITCH(TRUE(),'Table (2)'[East] >= 1 && 'Table (2)'[East] <= 2000, "#FF0000", // Red for East, 1 - 2000"#FFFFFF" // Default to white if none of the conditions are met)If any new columns or new rows are added just update the measures and it works.West Color =SWITCH(TRUE(),'Table (2)'[West] >= 1 && 'Table (2)'[West] <= 3000, "#FF0000", // Red for West, 1 - 3000'Table (2)'[West] > 3000 && 'Table (2)'[West] <= 5000, "#008000", // Green for West, 3000 - 5000"#FFFFFF" // Default to white if none of the conditions are met)After creating separate measures you can apply conditional formatting for each column by selecting Background color in cond formatting and secting field value and selecting respective color measure.North Color =SWITCH(TRUE(),'Table (2)'[North] >= 1 && 'Table (2)'[North] <= 3000, "#FF0000", // Red for North, 1 - 3000'Table (2)'[North] > 3000 && 'Table (2)'[North] <= 10000, "#008000", // Green for North, 3000 - 5000"#FFFFFF" // Default to white if none of the conditions are met)
Here is a screenshot for reference.After applying conditional formatting for each column the result looks like below image
If you find my solution helpful please give a kudos.
Thanks,
Vinay.
