Power BI Conditional Formatting

Conditional formatting is type of feature which are used to highlight cells of a data set in different type of colors based of certain or different conditions, this feature is used in both ms excel and power bi, there is a catch for conditional formatting in power bi and it is that we can manage it in the value area of the fields.

Conditional Formatting in Power BI

If you think Power BI is just limited to visualization and charts, then you will spend a considerable amount of time figuring out how the below colors or in cell bars have been applied. This is not a bar chart or some other visualization; rather, it is conditional formatting applied to the table visually. Let me tell you frankly, I have spent a considerable amount of time figuring out this, so here you go. This article will explain in detail the conditional formatting in Power BI.

Power-BI-Conditional-Formatting

You are free to use this image on your website, templates etc, Please provide us with an attribution linkHow to Provide Attribution?Article Link to be Hyperlinked
For eg:
Source: Power BI Conditional Formatting (wallstreetmojo.com)

To apply Power BI Conditional Formatting, you need data to work with, so you can download the excel workbook template from the below link, which is used for this example.

You can download this Power BI Conditional Formatting Excel Template here – Power BI Conditional Formatting Excel Template

How to Apply Conditional Formatting in Power BI with Examples

Follow the below steps to apply conditional formatting.

  1. Below is the table I have already created in the Power BI reportPower BI ReportPower bi reports are detailed visualizations of data that are presented differently than common reports. These reports can be multi-page or single-page, with visuals on each page.read more tab.

    Conditional Formatting in Power BI (Table)

  2. For the table, I have drag and dropped “Sale Value, COGS, Discounts, and Gross ProfitGross ProfitGross Profit shows the earnings of the business entity from its core business activity i.e. the profit of the company that is arrived after deducting all the direct expenses like raw material cost, labor cost, etc. from the direct income generated from the sale of its goods and services.read more” columns from the table.

    Conditional Formatting in Power BI (Drag all Data)

  3. To apply conditional formatting in Power BI to this table under the field section of the table, click on the “drop-down” icon of “Sale Value.”

    Click on Sales Value

  4. When you click on that down arrow, you will see the below options for this column.

    Show Options

  5. From the above list, just hover on “Conditional Formatting,” It will further show many conditional formatting options in Power BI.

    Choose Conditional formatting

As you can see above, we have four kinds of conditional formatting types, “Background Color, Font Color, Data Bars, and Icons.”

We will see one by one for all the four columns of our table.

Example #1 – Using Background Color

  • First, we will apply the background color for the “Sale Value” column. Once again, click on the down arrow of the “Sale Value” column and choose Conditional Formatting and under this, choose “Background Color.”
choose Background color
  • This will opens up below the formatting window.
Open formatting window
  • Under the “Format By” drop-down list, we can choose various other types for this category of conditional formatting and leave it as “Color Scale” only.
Choose Color Scale
  • Then make sure “Based on Field” is the “Sum of Sale,” “Summarization” as “Sum” and “Default formatting” as “Zero.”
Fill conditional formatting
  • Now for “minimum values,” it has already chosen a “Red” color, and for the “maximum” value, it has chosen a light “blue” color.
choose Color
  • If you want to have one more color for middle values, check the box “Diverging” to get the middle-value section.
Get the middle-value
  • Now click on “Ok,” and we will have background color as follows.
Conditional Formatting in Power BI (Background Color)

Example #2 – Using Font Color

  • For the “COGS” column, we will apply “Font Color” conditional formatting. From conditional formatting, choose “Font Color.”
Choose Font Color
  • This will opens up below the window.
opens font color window
  • This is similar to the one we have seen above, but this time, as usual, you choose your interesting colors. I have chosen the below color.
Choose Font Color Again
  • After choosing the colors, click on “Ok,” and we will have below colors for “COGS” numbers in the table.
Conditional Formatting in Power BI (Fill COGS)

Example #3 – Using Data Bars

Choose Data Bars
  • This will opens up below the “Data Bars” conditional formatting window.
Open Data Bars
  • In this category, we can choose whether we want to show only “Bars,” or we want to show bars along with numbers.
Show Bar Only
  • Since we need to see numbers along with bars, let’s not check this box. Next, we can choose colors for “Minimum” & “Maximum” values.
choose colors
  • Choose the colors as you would like to and click on Ok, we will have below in cell charts or bars for the “Discount” column.
Conditional Formatting in Power BI (Data Bars)

Example #4 – Using Icon Sets

  • One final category under conditional formatting is “Icon Sets.” These are icons to show against numbers based on the rules specified,
  • Click on the down arrow of “Profit” and choose conditional formatting.
Choose Icons

This will opens up below the “Icons” window; under this, we will define what kind of “Icons” to show under different conditions.

opens Icon Options
  • Under the “Style” category, we can choose a variety of icons to show.
Choose Style
  • Choose the best fit for your data sets.
  • Under the rules section, we can number ranges or percentage ranges.
Choose Rules
  • You can set up as you need, and we will have below icons for the “Profit” column.
Conditional Formatting in Power BI (Fill Icons)

Note: To apply conditional formatting, we need data; you can download the Power BI file to get the ready table.

You can download this Power BI Conditional Formatting Template here – Power BI Conditional Formatting Template

Things to Remember

  • Conditional formatting is similar to the one in MS Excel.
  • We have four different kinds of conditional formatting options in Power BI.
  • We can define rules to apply conditional formatting in Power BI.

Recommended Articles

This has been a Guide to Power BI Conditional Formatting. Here we discuss how to apply conditional formatting power BI using background color, font color, etc., along with examples and downloadable templates. You can learn more about Power BI from the following articles –

  • 6 Online Courses
  • 29+ Hours
  • Verifiable Certificate of Completion
  • Lifetime Access
LEARN MORE >>