Power BI Conditional Formatting allows users to custom-code cells with respect to color and field values to better understand what significance a set of cells corresponds to in terms of information for better decision-making.
Extending on that, Power BI also allows users to picture data as data bars, KPI icons, and active weblinks. And, a step forward, it is possible to apply conditional formatting to any text or data field in Power BI as long as certain information is present in the form of, and as the Microsoft Documentation confers, has “a field that has numeric, color name or hex code, or web URL values.”
In this tutorial article, we will learn all about Power BI Conditional Formatting. And, to make a coherent understanding of the subject, learn about Power BI and features and Conditional Formatting in general. Let’s begin.
What is Power BI?
Power BI
Power BI — a set of software services, apps, and connectors — works as a tool that transforms data from multiple data sources into logical, visually immersive, and interactive insights for today’s organizations to make data-driven decisions. Data could be in an Excel spreadsheet or is a collection of hybrid data warehouses — both cloud-based and on-premises. In short, Power BI makes it straightforward to connect to data sources, visualize and uncover what matters for your business, share critical findings with whomever you desire.
Some Exquisite Features of Power BI are as Follows:
- Wide Array of Attractive Visualizations: In Power BI, visualizations or the visual representation of data, play a crucial role. It has a large number of detailed and appealing visualizations. You can make reports and dashboards with as few or as many visuals as you wish to describe your data set. A library for custom visualizations is also provided.
- A Multitude of Data Integration Options: Power BI users can choose from a variety of data sources using the Get Data function. The data sources can range from on-premise to cloud-based, unstructured to structured data. Every month, new data sources are added.
- Datasets Filtration Process: A dataset is a single set of data formed by combining data from many sources. The datasets can be used to build a variety of visualizations. A dataset might be made up of data from a single source, such as an Excel worksheet, or data from multiple sources.
- Dashboards are Comtomizable: Dashboards are a collection of data visualizations that provide useful information or insights. In Power BI, most dashboards are made up of tiles that contain numerous visualizations. They’re single pages taken from reports. The dashboards can be printed as well as shared.
What is Conditional Formatting?
Many spreadsheet software have a feature that allows you to apply custom formatting to cells that fulfill certain conditions known as conditional formatting. Conditional formatting is most commonly used to highlight, emphasize, or separate facts and information in a spreadsheet using color-based formatting.
Users of spreadsheets can do a variety of things with conditional formatting. First and foremost, it highlights critical information such as deadlines, high-risk assignments, and budget items. It can also break up a wall of statistics with a visual organizational component, making enormous data sets more palatable. Finally, conditional formatting can turn your spreadsheet (previously solely used to store data) into a reliable “alert” system that highlights important facts and keeps you on top of your job.
Other spreadsheet applications like PowerBI, Smartsheet, and Google Sheets have inherited this functionality, which was originally a powerful feature of Excel.
Hevo Data, a No-code Data Pipeline helps to transfer data from 150+ sources such as Power BI to a Data Warehouse/Destination of your choice and visualize it in your desired. Hevo is fully managed and completely automates the process of not only loading data from your desired source but also enriching the data and transforming it into an analysis-ready form without even having to write a single line of code. Its fault-tolerant architecture ensures that the data is handled in a secure, consistent manner with zero data loss.
It provides a consistent & reliable solution to manage data in real-time and always have analysis-ready data in your desired destination. It allows you to focus on the key business needs and perform insightful analysis by using a BI tool of your choice.
Get Started with Hevo for Free
Check out what makes Hevo amazing:
- Secure: Hevo has a fault-tolerant architecture that ensures that the data is handled in a secure, consistent manner with zero data loss.
- Schema Management: Hevo takes away the tedious task of schema management & automatically detects schema of incoming data and maps it to the destination schema.
- Minimal Learning: Hevo, with its simple and interactive UI, is extremely simple for new customers to work on and perform operations.
- Transformations: Hevo provides preload transformations through Python code. It also allows you to run transformation code for each event in the Data Pipelines you set up. You need to edit the event object’s properties received in the transform method as a parameter to carry out the transformation. Hevo also offers drag and drop transformations like Date and Control Functions, JSON, and Event Manipulation to name a few. These can be configured and tested before putting them to use.
- Connectors: Hevo supports 100+ Integrations from sources to SaaS platforms, files, databases, analytics, and BI tools such as Power BI. It supports various destinations including Amazon Redshift, Firebolt, Snowflake Data Warehouses; Databricks, Amazon S3 Data Lakes, SQL Server, TokuDB, DynamoDB databases to name a few.
- Hevo Is Built To Scale: As the number of sources and the volume of your data grows, Hevo scales horizontally, handling millions of records per minute with very little latency.
- Incremental Data Load: Hevo allows the transfer of data that has been modified in real-time. This ensures efficient utilization of bandwidth on both ends.
- Live Support: The Hevo team is available round the clock to extend exceptional support to its customers through chat, email, and support calls.
- Live Monitoring: Hevo allows you to monitor the data flow and check where your data is at a particular point in time.
Sign up here for a 14-Day Free Trial!
Power BI Conditional Formatting
Power BI Conditional Formatting is fairly easy to use and activating it is a one-click step. To apply Power BI Conditional Formatting in Power BI Desktop simply select a Table or a Matrix visualization. Now in the Visualization pane, you will have to select by right-clicking the down-arrow next to the “Values” field. Now select conditional formatting and the type of formatting you want.
Credit: Microsoft Documentation
To remove Power BI conditional formatting from a visualization, go to the field’s drop-down menu and select “Remove conditional formatting,” and then select the type of formatting you want.
Credit: Microsoft Documentation
How to Apply Power BI Conditional Formatting to a Format Background or Font Color?
Select Power BI Conditional formatting for a field and then either Background color or Font color from the drop-down menu to format cell background or font color.
Credit: Microsoft Documentation
The Background color or font color dialogue box will appear with the title containing the name of the field you’re formatting. Select OK after you’ve made your Power BI conditional formatting choices.
Credit: Microsoft Documentation
Background color and Font color are the same. However, they impact the cells’ background color and font color. The field’s font color and background color might have the same or distinct conditional formatting. When the font and background of a field are the same color, the type merges into the background, leaving only the colors visible in the table column.
How to Apply Power BI Conditional Formatting to a Color-code Using a Color Scale?
YOu can select the color scale in the Format section by field of the Background color or Font color dialogue box to format cell background or font color by color scale. Select the field on which the formatting will be based under “Based on the field”. The Power BI Conditional Formatting can be based on the current field or any other field in your model that contains numerical or color data.
Credit: Microsoft Documentation
Select the aggregation type you want to use for the specified field under “Summarization.” Select the formatting type to apply under Default formatting type.
Credit: Microsoft Documentation
The following is an example table with color scale background formatting in the Affordability column:
Credit: Microsoft Documentation
How to Apply Power BI Conditional Formatting to a Color by Rules?
Select Rules in the Format by field of the Background color or Font color dialogue box to format cell background or font color according to rules. Based on field displays select the field on which the formatting is based, and Summarization displays the field’s aggregate type.
Enter one or more value ranges and colors for each under Rules. There is an “If” value condition, a value condition, and a color for each value range. Each value range’s cell backgrounds or fonts are colored with the specified color. There are three rules in the following example:
Credit: Microsoft Documentation
If you choose Percent from this option, the rule boundaries will be specified as a percentage of the whole range of values from lowest to maximum. If the lowest data point was 100 and the highest was 400, the preceding rules would color anything under 200 green, anything between 200 and 300 yellow, and anything over 300 red.
The following is an example table with rules-based background color formatting dependent on the Percent column in the Affordability column:
Credit: Microsoft Documentation
How to Apply Power BI Conditional Formatting in Color by Rules for Percentages?
If the field on which the formatting is based contains percentages, write the numbers in the rules as decimals representing the actual values, such as “.25” instead of “25.” Also, instead of Percent, choose Number as the number format. “If the value is more than or equal to 0 Number and less than.25 Number,” for example, yields numbers less than 25%.
Credit: Microsoft Documentation
0 to 25% is red, 26 percent to 41 percent is yellow, and 42 percent and more is blue in this sample table with rules-based backdrop color on the percent revenue region column:
Credit: Microsoft Documentation
How to Apply Power BI Conditional Formatting in Color by Color Values?
If you have a field or measure with color names or hex values, you can use Power BI conditional formatting to apply to those colors with a background or font color of a column automatically. Custom logic can also be used to add colors to the font or a background.
Each state is assigned a color name in the table below:
Credit: Microsoft Documentation
Select “Conditional formatting” for the Color field, then background color or Font color to format the Color column based on its field values.
Select Field value from the Format by drop-down field in the background color or font color dialogue box.
Credit: Microsoft Documentation
The following is an example table using color field value-based Background color formatting in the Color field:
Credit: Microsoft Documentation
If you additionally use Field value to format the font color of the column, the color column will have a solid color:
Credit: Microsoft Documentation
How to Apply Power BI Conditional Formatting to a Color Based on a Calculation?
You can use business logic conditions to generate a calculation that outputs distinct results. Creating a formula is frequently faster than using the Power BI conditional formatting dialogue to create several rules.
For example, depending on current Affordability column values, the following algorithm assigns hex color values to a new Affordability rank column:
Credit: Microsoft Documentation
Select Background color or Font color conditional formatting for the Affordability column, and then base the formatting on the Field value of the Affordability rank column to apply the colors.
Credit: Microsoft Documentation
The following is an example table with an Affordability background color based on the estimated Affordability rank:
Credit: Microsoft Documentation
How to Apply Power BI Conditional Formatting for Adding Data Bars?
Select “Conditional formatting” for the Affordability field, then Data bars from the drop-down box to display data bars based on cell values.
The Show bar’s only option is unchecked by default in the Data bars dialogue, so the table cells display both the bars and the actual numbers. Select the Show bar only after checking the box to only show the data bars.
You can customize the Minimum and Maximum values and the colors and direction of the data bars and the axis color.
Credit: Microsoft Documentation
The example table looks like this with data bars appended to the Affordability column:
Credit: Microsoft Documentation
How to Apply Power BI Conditional Formatting to Add Icons?
Select Conditional Formatting for the field, then Icons from the drop-down box to display icons depending on the cell values.
Select Rules or Field value under the Format, in the Icons dialogue.
Choose a Based on field, Summarization method, Icon layout, Icon alignment, icon Style, and one or more Rules to format by rules. Select an icon to apply to each rule and input one or more rules with an If value condition and a value condition, present under the Rules.
Select a “Based on field,” “Summarization method,” “Icon layout,” and “Icon alignment” to format by field values.
The following example uses three rules to add icons:
Credit: Microsoft Documentation
Choose OK. The sample table looks like this with icons assigned to the Affordability column by rules:
Credit: Microsoft Documentation
How Apply Power BI Conditional Formatting to Format as Web URLs?
You can use Power BI conditional formatting to apply website URLs to fields as active links if you have a column or measure that contains them. The following table, for example, has a Website column with website URLs for each state:
Format as Web URLsCredit: Microsoft Documentation
Select conditional formatting for the State field, then Web URL to show each state’s name as a live link to its Website. Select Website Based on the field in the Web URL dialogue box, then click on OK.
Each state’s name is an active link to its website when Web URL formatting is added to the State field. The state column in the concurrent sample table contains Web URL formatting options, while the overall rank column has conditional data bars.
Credit: Microsoft Documentation
How to apply Power BI Conditional Formatting Totals and Subtotals?
Totals and subtotals can be formatted using conditional formatting rules in both table and matrix graphics.
The Apply to drop-down in Power BI conditional formatting is used to apply the conditional formatting rules, as seen in the accompanying image.
Credit: Microsoft Documentation
The thresholds and ranges for Power BI conditional formatting rules must be manually set. Values refer to the lowest detectable level of the matrix hierarchy in matrices.
How to apply Color-code Power BI Conditional Formatting Based on Text?
You can’t color-code text values to appear as a specific color, like “Accepted” =blue, “Declined” =red, or “None” =grey, like you can in Excel. Instead, you develop measures based on these variables and apply them to the format.
SWITCH(‘Table'[Status], “Accepted”, “blue”, “Declined”, “red”, “None”, “grey”), for example.
The Status field is then formatted in the Background color dialogue box based on the values in the StatusColor field.
Credit: Microsoft Documentation
The value determines the formatting in the resulting table in the StatusColor field, which is determined by the text in the Status field.
Credit: Microsoft Documentation
Conclusion
In this tutorial article, we have gone into detail about Power BI Conditional Formatting, its features, and how to use those features. Moreover, we have also discussed in detail the basics of conditional formatting and Power BI at the start. If you want to learn more about how Power BI Conditional Formatting works, I would recommend either of these two articles:
- Use conditional formatting in tables
- Tips and tricks for formatting in reports
Extracting complex data from a diverse set of data sources to carry out an insightful analysis can be a challenging task and this is where Hevo saves the day! Hevo Data, a No-code Data Pipeline can seamlessly transfer data from a vast sea of 150+ sources to a Data Warehouse, BI Tool like Power BI, or a Destination of your choice. It is a reliable, completely automated, and secure service that doesn’t require you to write any code!
Visit our Website to Explore Hevo
If you are using Power BI as your Data Analytics & Business Intelligence platform and searching for a no-fuss alternative to Manual Data Integration, then Hevo can effortlessly automate this for you. Hevo, with its strong integration with 150+ Data sources and BI tools such as Power BI (Including 40+ Free Sources), allows you to not only export & load data but also transform & enrich your data & make it analysis-ready in a jiffy.
Want to take Hevo for a ride? Sign Up for a 14-day free trial and simplify your Data Integration process. Do check out the Hevo pricing details to understand which plan fulfills all your business needs.
Yash is a Content Marketing professional with over three years of experience in data-driven marketing campaigns. He has expertise in strategic thinking, integrated marketing, and customer acquisition. Through comprehensive marketing communications and innovative digital strategies, he has driven growth for startups and established brands.
