fbpx
Search
Close this search box.

How to conditionally format visuals in Power BI?

Share

Facebook
Twitter
LinkedIn

Do you know that you can apply conditional formatting rules to visuals in Power BI? In this post, let’s learn how to conditionally format visuals in Power BI. Something like this:

How to conditionally format visuals in Power BI

To conditional format charts in Power BI, follow below instructions.

  1. Select the visual you want to apply formatting rules.
  2. Go to Format pane and click on “Data colors”.
  3. When you hover on the label “Default color”, you notice three dots. Click on them to activate “Conditional format” screen.
Activating conditional formatting feature from data colors - demo
  1. Here you can define a rule for coloring the chart. There are 3 types of conditional formats (as of writing this post in last week of July, 2019). They are,
    1. Color scales
    2. Rules
    3. Field value

Color scales:

As the name suggests, this will create a continuous color scale from minimum to maximum value. You can opt for diverging scale to include a midpoint color too. I am not a big fan of this for normal charts (although I do use it on tables / matrix visuals).

Color scale rule for conditional formatting in visuals - demo

Rules:

This gives you flexibility to define a rule based logic for coloring. You can write rules to color chart elements based on number or item rules. See this example to understand how to set up a rule to highlight all cities where “Quantity per Transaction” is less than 8.2 in different color.

Adding rule based conditional formatting - example
Sample rules in Power BI visual conditional formatting

This is how our final chart would look:

Final output of conditional formatting based on rules
Demo of Conditional Formatting in Power BI based on rules

Field Value:

You can also write a measure (or have a table field) that returns HEX code (or RGB code) for the color you want to use for each chart element. If you have such a measure, you can use Field value based rules to set them. For example, you can create a measure like [Column Color] and link it.

[Column Color] = IF([Quantity per Transaction]>8.2, "#999999", "#c82222")

When you link this [Column Color] measure to the chart on Field value rule, your columns will be either of those colors (#999999 or #c82222) based on the [Quantity per Transaction] for each item in the chart.

How to make rules based on dynamic values?

As of now, the conditional formatting in Power BI only accepts rules based on fixed (hardcoded) values. But we can create an intermediate measure to overcome this limitation. For example, we can create a measure like this:

[Rule outcome for highlighting] = IF([Quantity per Transaction] > [QpT Target], 1, 0)

We can then use [Rule outcome for highlighting] in the rules and color based on the fixed values of 0 or 1.

How to conditionally format visuals in Power BI – Video tutorial

If you are still confused or need some guidance, please watch this 10 minute video. It explains all 3 types of conditional formatting with examples. Watch the video below or on our YouTube Channel.

Download Sample Workbook – Conditional formatting in Power BI

Please download the sample file for this tutorial here. You can see a demo of all three types of rules. Feel free to examine the data model, measures or create something on your own to learn how it all works.

Learn more about conditional formatting in Power BI:

Microsoft has been enhancing conditional formatting feature in Power BI over time. Just recently (in July 2019 release) they have launched support for icon rules (similar to Excel conditional formatting icons) for tables & matrix visuals. Learn more about conditional formatting in Power BI by reading Power BI docs.

More Power BI examples & tutorials:

Do you use conditional formatting in Power BI?

I have been using CF more often in my Power BI training / work as it is a powerful way to highlight important information without cluttering your reports.

What about you? Have you toyed with conditional formatting in Power BI? Got any interesting tips to share? Please post them in the comments.

Facebook
Twitter
LinkedIn

Share this tip with your colleagues

Excel and Power BI tips - Chandoo.org Newsletter

Get FREE Excel + Power BI Tips

Simple, fun and useful emails, once per week.

Learn & be awesome.

Welcome to Chandoo.org

Thank you so much for visiting. My aim is to make you awesome in Excel & Power BI. I do this by sharing videos, tips, examples and downloads on this website. There are more than 1,000 pages with all things Excel, Power BI, Dashboards & VBA here. Go ahead and spend few minutes to be AWESOME.

Read my storyFREE Excel tips book

Overall I learned a lot and I thought you did a great job of explaining how to do things. This will definitely elevate my reporting in the future.
Rebekah S
Reporting Analyst
Excel formula list - 100+ examples and howto guide for you

From simple to complex, there is a formula for every occasion. Check out the list now.

Calendars, invoices, trackers and much more. All free, fun and fantastic.

Advanced Pivot Table tricks

Power Query, Data model, DAX, Filters, Slicers, Conditional formats and beautiful charts. It's all here.

Still on fence about Power BI? In this getting started guide, learn what is Power BI, how to get it and how to create your first report from scratch.

6 Responses to “How to conditionally format visuals in Power BI?”

  1. Chris says:

    Hi Chandoo,
    When I hover over the Data Colors, I don't get the 3 dots. I've tried every bar chart visual and I have the August 2019 version of Power BI Desktop. Can you think of a reason why conditional formatting won't show up for me?
    Thanks,
    Chris

  2. Marcus says:

    Yes I have the same problem. All 12 points on the scatter chart have different colors. Each dot has its own color select box. There is no "Default color" selection and no ellipses when hovering over anything.

  3. anu says:

    thanks a lot. very helpful.

  4. Eric says:

    Thanks for providing the answer but Power BI has updated its version in 2020, Now the options has switched its place. Version that I am using has not the options at the place mentioned in your article. It would be great if you can provide solution on updated version too.

  5. Leomar says:

    Hi, what version of Power BI your referring to?

  6. Sibi Mathews says:

    I am looking to create a flashing dot or circle on the Map based on zipcode. eg. I want to flash the dot for zipcodes that have zero sales.

Leave a Reply