1. Home
  2. Training Library
  3. Microsoft Azure
  4. Courses
  5. From Data to Insights With Power BI

Conditional Formatting

Contents

keyboard_tab

The course is part of this learning path

Conditional Formatting
Overview
Difficulty
Intermediate
Duration
59m
Students
96
Ratings
4.3/5
starstarstarstarstar-half
Description

Power BI allows business users to analyze data and share insights across all levels of an organization. It gives an end-to-end view of important metrics and key performance indicators through intuitive and interactive dashboards all in one place.

In this course, you will learn about several Power BI tools that would help you enhance reports to expose insights and present them in a way that appeals to a wide range of end-users.

Learning Objectives

  • Understand how to apply conditional formatting, slicers, and filters
  • Perform top N analysis
  • Explore statistical summary
  • Use the Q&A visual
  • Add a Quick Insights result to a report
  • Understand when and where to create reference lines by using the Analytics pane
  • Learn when and where to use the Play Axis feature of a visualization
  • Understand how to personalize visuals

Intended Audience

  • Beginners to data analytics
  • Business analysts
  • Business intelligence developers
  • Business intelligence managers
  • Anyone who wants to learn about Power BI

Prerequisites

  • Microsoft Power BI Desktop for PC/Windows users (free download)
  • Familiarity with preparing data using Power BI
  • Familiarity with modeling data using Power BI
  • A basic understanding of Power Query, Power Pivot, and DAX is a plus but not required
Transcript

Let's talk about Power BI report formatting options and conditional formatting. Sometimes in our reports we would want to highlight certain values in our data. Perhaps it's a sales figure for a new product that was launched or assessing quality scores after introducing a new service or evaluating the number of days before employees retire. With conditional formatting, our visualizations will benefit from dynamically setting colors based on the numeric value of a field in our data tables to highlight data points that are above or below a given value, such as areas of low or high sales.

In this lecture we will specify customized colors on table cells as well as bar charts based on font color gradient, rules and field values. So, in order to get started, let's go to power BI desktop and connect to our sample report. The way we color our visuals can convey an important message about our data and conditional formatting can be used on top of that in order to convey that message even clearer. So, we'll start by going to this table chart which is showing our Total Sales by Fiscal Month and District Manager. We see low sales in January compared to March.

Let's turn this into a bar chart and remove the District Manager fr om the legend to see the total sales by fiscal month. And let's apply conditional formatting directly with the format visual pain. So, we'll scroll down to Columns, hover over our colors. We see the fx button and if we click on it is going to open up this new window and we will be able to conditionally format these default blue and red colors. And if we were using a different visualization, sometimes you're able to apply conditional formatting directly to the value, not the case for this bar chart. But I do want to let you know that sometimes the conditional formatting is hiding in different areas. We see here that it's going to format by color gradient.

We'll have some options here and is currently based on the count of fiscal month field. We actually want to use our TotalSales instead. Let's use Search for sales, go to TotalSales and the gradient is set to the Minimum with the lowest value as red. Let's change that to light blue and the Maximum highest value is blue, let's change that to dark blue. We can click on 'Add a middle color' if you want to get a nice middle tone in there but we probably don't want that for our data. So, let's click 'OK'. And now we'll see that those seven columns have been faded because they're are much lower than the March sales bar. And now let's turn this back into a table matrix. And again applying conditional formatting directly with the format visual pain.

So, we'll scroll down to Cell elements, hover over our Background color, let's turn it on and click on the fx button and we'll use the same format by color gradient. Let's change Minimum to light blue and Maximum to dark blue. Let's click 'OK'. And we'll see that these eight rows have been faded according to their respective sales. Let's turn our table back to a bar chart and now let's format it by rules instead. We'll go back to the Format Visual pain, Columns, Colors, then Conditional Formatting. This time the format style will be on Rules and we want to use our total sales instead.

Let us search for sales and type 'totalsales', and let's do something like if it's greater than or equal to six million and we want to make sure it's a number instead of a percent and less than. And now if you backspace we'll have the Maximum show up. We can make that green. So, let's choose a custom green. Now, let's add another Rule. Let's say if it's greater than or equal to Minimum number, pick Number, and less than six million, we will make that red. Let's make sure that looks okay if it's greater than or equal to six million, less than Maximum, make it green. If it's greater than or equal to Minimum number and less than six million make it red.

Now, we should be good to go. Let's click 'OK' and see if it works. And we do see that our bar chart is now conditionally formatted. So, our values larger than six million are showing as green. The rest are lower than that, they are shown as red. These are just a couple of examples on how we can color our data dynamically based on a value. And do keep in mind if we do apply slicers here, this will change how this is going to work. I have a District Manager slicer here. Let's filter this down to Andrew Ma. We can see that all the bars are showing as red because the sales are obviously less than six million and they're all conditionally formatted.

Now, let's cover another instant of conditional formatting because there's a lot of options available to us. And I'm going to show you how to conditionally format based on field value. And I'm also going to show you how to conditional format a line chart which isn't actually available out of the box. So, firstly I'm going to unfilter our visuals. Let's go ahead and create a New measure and we're going to name this measure Bar Chart Conditional Formatting, and we're going to set this equal to an if statement. So, if the total sales, let's click on it, are greater than or equal to six million then make the bar green. And what we actually want to do here is we need to return a text value. And this is going to be a hex color code and the hex code is going to tell Power BI what color we want to assign to a data point.

So, for colors we actually think of the hex code as RGB. So, the green color is #00ff00, and if it's lower than six million return red, and that is #ff0000. And let's close that of, press 'Enter' and now we need to apply that to our bar chart. So, we come to the Format Visual Pain of our bar chart. Let's go to Column, go to Colors and click on the fx. It'll open the conditional formatting and I do want to make one point, if you get to this point and we can't click on our measure, it means it's not a text value. And we will need to return a text value, not a number or an ything like that has to be a text value to be able to select it based on a field selection. Once we click 'OK' those bars will be formatted. And please mind the very annoying green, this is just a default green.

And the second trick I want to demonstrate the ability to conditionally format a bar chart into a line chart. Basically the only step we have left to do is to switch this to a line chart. So, if we click on the 'Line Chart' button we will now see that these data markers are conditionally formatted based on the logic that we applied in the bar chart. Not sure how long this is going to be in Power BI but it has been there for the last few years so it might be here to stay. So, in this lecture we covered conditional formatting based on font color gradient, rules and field value, allowing for a ton of flexibility for a conditional formatting visualizations based on some pretty specific logic or some intense calculations.

 

About the Author
Avatar
Moatasim Al Khatib
Data and Insight Consultant
Students
94
Courses
1

Moatasim has been a data and insight consultant since 2014, driving data culture strategies in enterprises, non-profit organizations and tech startups to improve their decision making. He has teamed up with Fortune 1000 companies, MBB and Big Four consultants on complex engagements in government and private sectors. He has been a data analyst, business analyst, BI manager, and instructor. To date, Moatasim has created learning content relating to business intelligence, data analysis and machine learning, mostly within Power BI, Azure, SQL and Python. His hobbies included heavy metal drumming and meditation.