Trending September 2023 # Conditional Formatting In Power Bi Tables And Matrices # Suggested October 2023 # Top 18 Popular |

Trending September 2023 # Conditional Formatting In Power Bi Tables And Matrices # Suggested October 2023 # Top 18 Popular

You are reading the article Conditional Formatting In Power Bi Tables And Matrices updated in September 2023 on the website We hope that the information we have shared is helpful to you. If you find the content interesting and meaningful, please share it with your friends and continue to follow and support us for the latest updates. Suggested October 2023 Conditional Formatting In Power Bi Tables And Matrices

Conditional formatting in Power BI visuals allows you to draw attention to, or highlight, data in text or numeric fields using color, icons or data bars.

Let’s look at how to apply all of these different types of conditional formatting.

Watch the Video

Download Example Power BI File

Enter your email address below to download the sample PBIX file.

By submitting your email address you agree that we can email you our Excel newsletter.

Please enter a valid email address.

Download PBIX file

Background Color

This example data shows some random values for cities.

From here you have to choice to remove individual types of formatting or remove all.


Having chosen background color you then see this window which has the default Gradient chosen. You can also format by rules or by a field value (a measure or calculated column), I’ll look at those options later but for now let’s stick with gradient.

Formatting using a color gradient means that the lowest value is set to one color, and the highest value is set to another color. All values in between are set to some shade/mix of these two colors depending on how close they are to the minimum and maximum values.

You can apply the formatting to either:

Values only

Values and Totals

Totals Only

Where it asks what field to base the formatting on, most of the time it will be the same field that you are actually formatting. But you can have cases where you might format one field (like a text column/measure) based on the value of some other numeric field (column/measure).

The summarization to use is probably better described as what aggregation to use, for the value that the formatting is being based on, e.g. sum, min, max etc.

For empty values you can format these

As zero

With a specific color

With no formatting

The Minimum and Maximum values to use can be either the minimum and maximum found in the field, or you can set specific values for one of both of these.

Colors can be chosen for the minimum and maximum values and these determine the color gradient that will appear across all values.

You can also set a middle color for your values. If you activate this then you can choose a specific middle value or use the default option and let Power BI work out what the middle value is in your range of values.

Rules : Numbers

If you want to use rules then re-open the background Color formatting settings and change the format style to Rules

You can now create rules that determine what colors to assign to values based on bands of values.

As you can see in the image above, anything between the the Minimum value and 0.4 is red, anything between 0.4 and 0.8 is light blue and everything else up to the maximum value is green.

Saving these rules results in this formatting

Rules : Percentages

You can also format using rules and percentages. It’s important to understand how formatting using percentages across a range of values works.

I’ll use this simplified table of data for this example

Opening the formatting again I modify the rules to this

0% to 30% = Red

Applying this gives this formatting

If it’s not clear why the colors are applied this way let me explain.

Across my range of values, 2 is treated as 0%, 8 is 100%.

That means that 4 is 1/3 of the way to the maximum value of 8, therefore 4 is 33%.

Similarly 6 is 2/3 of the way to 8, therefore 6 is 66%.

Looking at the rules I created

0% to 30% = Red. 2 is the only value less than 30%

Field Value

Formatting using field values means using a measure or calculated column to assign the formatting. I prefer using measures. If you use a calculated column you’re adding data to your data model that probably doesn’t need to be there.

A measure to create formatting for a background color could look like this

The color names can be

Known CSS colors, more info here

Hex color codes e.g. #75AD21

RGB color codes e.g. RBG(200,50,25)

NOTE: Microsoft state that you can use HSL/HSLA color codes too but I can’t find examples and can’t get them to work.

To apply this measure, open the background color formatting settings, set the Format style to Field value and choose the measure from the What field …. ? drop down.

If you want to change these colors it’s easy too. Edit the measure and change the colors as shown

After saving the measure the formatting is changed. No need to go back into the conditional formatting settings and change anything.

Font Colors

With fonts, you have exactly the same options as you do with background colors.

One trick to achieve a cell of a single color is to set the font and background to the same color, like this

With the background formatting removed the colored fonts look like this

Data Bars

Data bars give you a colored bars, like a bar chart. You can set colors for the positive values and the negative values, based on the maximum and minimum values in your data range. You can set custom min and max values if you wish.

By default the bars are ‘Left to right’ which means you get positive values on the right of the ‘axis’ and negative values to the left.

The axis is a thin bar that separates the positive and negative data bars.

Using the settings above I get this

You can turn off the data values if you wish by checking ‘Show bar only’, it might make things look a little neater

If you want the bars running Right to left, choose that option in the settings


Icons can be displayed either by creating rules or using a field value. The configuration options are either the same, or very similar to what I’ve already covered.

Using these settings

gives this in my table

If you want to just display an icon and not the data values, set the Icon layout to Icon only

Custom Icons

By default, Power BI provides you with lots of icons

But if you want to use your own, you can create a measure or calculated column that points to a web accessible icon – store the icons on a web server somewhere.

I created a measure that assigns a bad, neutral or good icon depending on the values in the Performance column.

You can use .ico, .png, .jpg and .gif files for the icons


Adding conditional formatting with URL’s creates a link to a web location. That could be more information on something, a report related to the data or whatever you want.

Let’s say I want to link to a web page about each city. I need to create a measure or calculate column that gives the URL to the web page/location I want to link to.

Here’s my measure

Open the URL formatting options for the City column and configure the settings like this

You're reading Conditional Formatting In Power Bi Tables And Matrices

Update the detailed information about Conditional Formatting In Power Bi Tables And Matrices on the website. We hope the article's content will meet your needs, and we will regularly update the information to provide you with the fastest and most accurate information. Have a great day!