fbpx

How to conditionally format visuals in Power BI?

Share

Share on facebook
Facebook
Share on twitter
Twitter
Share on linkedin
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.

Share on facebook
Facebook
Share on twitter
Twitter
Share on linkedin
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

Chandoo is an awesome teacher
5/5

– Jason

Excel formula list - 100+ examples and howto guide for you

100 Excel Formulas List

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

20 Excel Templates

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

IRR and data tables in Excel

Using IRR with Data Tables – Modeling Cash-flow Scenarios in Excel

Do you want to simulate multiple cash-flow scenarios and calculate the rate of return? Then this article is for you. In this page, learn how to,

  • Introduction to IRR & XIRR functions
  • Calculate rate of return from a set of cash-flows with XIRR
  • Simulating purchase or terminal value changes with data tables
  • Apply conditional formatting to visualize the outputs
  • Common issues and challenges faced when using XIRR

One Response 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

Leave a Reply