WallStreetMojo

WallStreetMojo

WallStreetMojo

MENUMENU
  • Free Tutorials
  • Certification Courses
  • Excel VBA All in One Bundle
  • Login
Home » Excel, VBA & Power BI » Learn Power BI » Power BI Conditional Formatting

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

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

  • Below is the table I have already created in the Power BI report tab.

Conditional Formatting in Power BI (Table)

  • For the table, I have drag and dropped “Sale Value, COGS, Discounts, and Gross Profit” columns from the table.

Conditional Formatting in Power BI (Drag all Data)

  • 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

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

Show Options

  • 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.”

Popular Course in this category
Sale
Power BI Training Certification
4.5 (521 ratings)
6 Online Courses | 29+ Hours | Verifiable Certificate of Completion | Lifetime Access
View Course

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

  • Data bars look like an “in-cell” chart based on the value of the cell value. For the “Discounts” column, we will apply “data bars.”
  • Click on the down arrow of “Data Bars” and choose Conditional Formatting.” Under this category of “Conditional Formatting,” choose “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 –

  • Gantt Chart Using Power BI
  • Power BI Icons
  • Power BI Visuals
  • How to use Power Query in Excel?
0 Shares
Share
Tweet
Share
Power BI Training Certification
  • 6 Online Courses
  • 29+ Hours
  • Verifiable Certificate of Completion
  • Lifetime Access
LEARN MORE >>
Primary Sidebar
Footer
COMPANY
About
Reviews
Contact
Privacy
Terms of Service
RESOURCES
Blog
Free Courses
Free Tutorials
Investment Banking Tutorials
Financial Modeling Tutorials
Excel Tutorials
Accounting Tutorials
Financial Statement Analysis
COURSES
All Courses
Financial Analyst All in One Course
Investment Banking Course
Financial Modeling Course
Private Equity Course
Venture Capital Course
Excel All in One Course

Copyright © 2021. CFA Institute Does Not Endorse, Promote, Or Warrant The Accuracy Or Quality Of WallStreetMojo. CFA® And Chartered Financial Analyst® Are Registered Trademarks Owned By CFA Institute.
Return to top

WallStreetMojo

Free Excel Course

Excel functions, Formula, Charts, Formatting creating excel dashboard & others

* Please provide your correct email id. Login details for this Free course will be emailed to you

Book Your One Instructor : One Learner Free Class
Let’s Get Started
Please select the batch
Saturday - Sunday 9 am IST to 5 pm IST
Saturday - Sunday 9 am IST to 5 pm IST

This website or its third-party tools use cookies, which are necessary to its functioning and required to achieve the purposes illustrated in the cookie policy. By closing this banner, scrolling this page, clicking a link or continuing to browse otherwise, you agree to our Privacy Policy

WallStreetMojo

Download Power BI Conditional Formatting Template

By continuing above step, you agree to our Terms of Use and Privacy Policy.

WallStreetMojo

Free Excel Course

Excel functions, Formula, Charts, Formatting creating excel dashboard & others

* Please provide your correct email id. Login details for this Free course will be emailed to you

Login

Forgot Password?

WallStreetMojo

Download Power BI Conditional Formatting Excel Template

New Year Offer - Power BI Training Certification (22+ Hours of Training) View More