Power BI Conditional Formatting

I thought this would be an easy blog. Not much to say about using Power BI’s Conditional Formatting. All it does is make visuals look more appealing by using pretty colours based on the data’s values. How wrong I was! In fact there’s so much to say that I’ve had to resort to a mini contents page using outline numbering and links to the relevant paragraphs. In fact, just like the old days of writing user manuals!

So here goes with the contents of this blog:-

1. Conditional Formatting for Table and Matrix Visuals
1.1. Using Background Color or Font Color
1.1.1. Format Background or Font by Color Scale
1.1.2. Format Background or Font by Rules
1.1.3. Formatting Blank Values
1.1.4. Using a Different Column or Measure as your “Based on field”

1.2. Using Icon Sets

1.3. Using Data Bars

1.4 . Using Web URL

2. Conditional Formatting on Chart Visuals
2.1 Format Chart by Color Scale
2.2 Format Chart by Rules
2.3 Using Conditional Formatting for Backgrounds and Borders

3. Using the “Format by Field Value” Option.
3.1. Using a Column as a Field Value
3.2. Using a DAX measure as a Field Value

4. Using Conditional Formatting to Create Dynamic Titles for Visuals
4.1. Using a Column to create the Title
4.2. Using a DAX Measures to create the Title

1. Conditional Formatting for Table and Matrix Visuals

In this example, I’m using a Table Visual that comprises a column for the Wine names and a measure called “Total Sales”:-

A pretty boring set of numbers you might say so let’s spice it up! I could use different colours to identify high and low selling wines by total sales and so for this I’m going to explore various different Conditional Formatting options.
To apply Conditional Formatting for both Tables and Matrices, you have a “Conditional formatting” card on the Paint Roller. Just remember to find the correct field to be formatted in the dropdown, in my case, I’ve chosen the “Total Sales” DAX measure:-

Note: It’s frustrating that with Matrix Visuals, you can only apply Conditional Formatting to fields sitting in the “Values” bucket and not to fields in “Rows” or “Columns”.

1.1 Using Background Color or Font Color

Firstly, I’m going to explore what I can achieve using either a Background color or a Font color. In this example, I have chosen “Background color”:-

1.1.1. Format Background or Font by Color Scale

When I turn on “Background color” or “Font color”, I immediately get the Total Sales formatted using a Color scale:-

Default Background Color	Default Font Color
Image	Image

However, I may not like these colours. No, I definitely don’t like these colours! So I can click on “Advanced controls” and I then get these “Background” colour (or “Font” colour) options:-

Key:-

1. Notice we’re using “Format by Color scale”.
2. The “Based on field” is usually the same field or measure that you want to format, in our case the measure “Total Sales”. However, you can base the formatting on values in a different column or measure, see 1.1.4. “Using a Different Column or Measure as your “Based on field”.
3. Any blank values will be formatted as zero unless you pick a different option in this dropdown. You can select a different colour or no formatting.
4. Select the colours you want for Minimum and Maximum.
5. If you want a “Center” colour, select Diverging.

This is the result of my custom colour scales for both Background and Font. A big improvement, I think you’ll agree:-

Background Color	Font Color
Image	Image

1.1.2 Format Background or Font by Rules

I’ve now decided that the color scales don’t do what I want because I want to identify “good” sales which are greater that £4,000,000 and “bad” sales that are less than £2,000,000. It’s difficult to see this using the color scale. If I use “Format by Rules” instead, this should give me what I’m after:-

Key:-

1. Notice we’ve change this to “Format by Rules”.
2. The “Based on field” is usually the same field or measure that you want to format, in our case the measure “Total Sales”. However, you can base the formatting on values in a different column or measure, see 1.1.4 “Using a Different Column or Measure as your “Based on field”.
3. Under Rules and after “If value”, you can select:-

is greater than or equal to
is greater than
is
Is blank – see 1.1.3. Formatting Blank Values

Enter the number or percent you want for your test and don’t forget to change this to “Number” if you don’t want “Percent”. If you leave this blank, it will default to “Minimum”.
Then supply the “is less than” number or percent( if required) or leave this blank and it will default to “Maximum”. Now you can select your colour for this rule.
4. If you want another rule, select New Rule.

Note: If I used a numeric column for Total Sales instead of a measure, the Conditional Formatting options would also include the summarising function I want to use, (the default being “SUM”). I haven’t shown this option in this blog as the assumption is that you’re using DAX measures and if so, the function is implicit in the measure.

This is the result of the Conditional Formatting using the above rules:-

Background Color using Rules	Font Color using Rules	Background Color with the same Font Color
Image	Image	Image

1.1.3. Formatting Blank Values

By default blank or missing values are not formatted. If you want to format blank values, select “is blank” in your rule:-

Blanks not formatted by default	You can select “is blank” to format blanks
Image	Image

1.1.4. Using a Different Column or Measure as your “Based on field”

So in the example below, I’ve changed the field on which I want to apply the formatting to the Wine field. However, the measure used for the rule is still the same i.e. Total Sales.

In this example the Background Color is on the “Wine” column but I’m still using Total Sales as the Base Field:-

Here’s another example where the rule being used for the formatting uses a different field to where the formatting is applied. The rule is that the “No. of Sales” measure is less than 200, but the formatting is being applied to the Total Sales column:-

1.2 Using Icon Sets

Using colours can be a little bit boring! Why not jazz up these rules up by using Icon Sets. I want to use the same rules as I’ve used above but his time I want to use “Traffic lights” to identify the different sales ranges. I don’t want to show the Sales values, only the icon.

This is what I get when I turn on Icons and this is what I want:-

Turn on Icons	Default Icon Set	Traffic Lights Icon Set using “Icon Only”

To achieve my goal, I need to click on “Advanced Controls” and select these options:-

Key:-

1. Notice this is “Format by Rules”.
2. The “Based on field” is usually the same field or measure that you want to format, in our case the measure “Total Sales”. However, you can base the formatting on values in a different column or measure, see “Using a Different Column or Measure as your “Based on field”.
3. Notice I have selected “Icon Only”. The default is “Left of data”.
4. Icon alignment is only applicable if you have multiple lines of data in the same “cell” of the table and you may want change this to “Middle”.
5. Select the Style of icons. However, you can pick the icons individually for each rule. These are the icons you can choose from:-

6. Under Rules and after “If value”, you can select:-

is greater than or equal to
is greater than
is
Is blank – see above, Formatting Blank Values

Enter the number or percent you want for your test and don’t forget to change this to “Number” if you don’t want “Percent”.
Then supply the “is less than” number or percent( if required) or leave this blank and it will default to “Maximum”.
Now you can select the icon you want for this rule.
If you want another rule, select New Rule

1.3 Using Data Bars

However, I’ve now decided that a better way to show my Sales values is to be able to compare the comparative difference between the sales, a bit like you would get in a Bar Chart. This is where Data Bars might help as these will create a mini bar chart alongside the data. I’ve turned on Data bars and clicked on “Advanced controls”:-

These are the options that I have chosen:-

Key:-

1. Notice I’ve asked to show bar only.
2. Select the colour of the bar. If you have negative values, select the colour of these too.
3. Notice too that I have changed the direction of the bars.

Below you can see the difference between the default bars and my customised bars. I asked to “Show bar only”, but I still want to include the Total Sales values so I created a second version of Total Sales and placed it in the Table visual (Total Sales 2):_

Default Data Bars	My Customised Data Bars
Image	Image

1.4 Using Web URL

So now on to the last type of Conditional Formatting on the Conditional Formatting card; Web URL. So this has got nothing now to do with my Total Sales but because it’s a on the list of Conditional Formats, I thought I’d better cover it.

So what does this do? If you have a column or measure that contains website URLs as I have in the column called “Image”, you can use conditional formatting to apply active links to the URL, as I’ve done on the wine names in the WINE column:

Notice that I’m applying the conditional formatting to the WINE column:-

As soon as you select Web URL, these options appear:-

Key:-

1. You can only select the “Format by Field Value” option.
2. In the “Based on field” select the field that has the URL.

So that pretty much concludes Conditional Formatting on Tables and Matrix visuals. Indeed, in the early days of Power BI, we could only use Conditional Formatting on Tables and Matrices so I suppose that explains why when Conditional Formatting for chart type visuals appeared, we had to go about it in a different way. So now read on…….

2. Conditional Formatting on Chart Visuals

You can apply conditional formatting to the “Data colors” of the following Visuals:-

Clustered Column Chart (with no Legend)
Clustered Bar Chart
Scatter Chart
Funnel Chart
Tree Map
Map
Filled Map
Gauge – also to the “Target”
Card – to the following elements: Data label, Category, Title, Background & Border

You can also apply conditional formatting to the Background and Border of all Visuals.

The example I’m using here is a Clustered Column Chart showing Wine names on the X-Axis and using the Total Sales DAX measure as the Values:-

This column chart would be much improved if I could use colours to show both high and low value sales and so I’m going to explore various different kinds of Conditional Formatting to see which gives me the best result.

The trickiest part of all of this is finding the Conditional Formatting option! Here’s the secret; if you want to know if a particular visual element can use conditional formatting, just hover over the “Color” option on any formatting card (or Title option) and if you get an ellipsis (3 vertical dots), then you know you can select Conditional Formatting! Here’s an example:-

Let’s start our tour through chart type conditional formatting by looking at formatting the Data colors (i.e. the columns) of our Column Chart as “Color Scales”:-

2.1 Format Chart by Colour Scale

Key:-

1. Notice we’re using “Format by Color scale”.
2. The “Based on field” is usually the same field or measure that you want to format, in our case the measure “Total Sales”. However, you can base the formatting on values in a different column or measure, see 1.1.4. “Using a Different Column or Measure as your “Based on field” above.
3. If there are blank values, there would be no column in the chart so this wouldn’t apply.
4. Select the colours you want for Minimum and Maximum.
5. If you want a “Center” colour, select Diverging.

This is the chart I now get:-

A Simple Color Scale Applied to a Column Chart

However, just as before, I want to identify “good” sales which are greater that £4,000,000 and “bad” sales which are less than £2,000,000 but the color scales, although very pretty, don’t give this level of detail. So the next option I’lll try is using “Format by Rules”.

2.2. Format Chart by Rules

Key:-

1. Notice we’ve change this to “Format by Rules”.
2. The “Based on field” is usually the same field or measure that you want to format, in our case the measure “Total Sales”. However, you can base the formatting on values in a different column or measure, see 1.1.4. “Using a Different Column or Measure as your “Based on field” above.
3. Under Rules and after “If value”, you can select:-

is greater than or equal to
is greater than
is
Is blank – although I’m not sure how this would be used.

Enter the number or percent you want for your test and don’t forget to change this to “Number” if you don’t want “Percent”.
Then supply the “is less than” number or percent( if required) or leave this blank and it will default to “Maximum”.
Now you can select your colour for this rule.
4. If you want another rule, select New Rule

This is what my column chart now looks like and I’ve also shown what a Scatter chart and a Treemap would look like with the same rules:-

A Clustered Column Chart using Rules

A Scatter Chart using the same Rules

A Treemap using the same Rules.

Here you can also override the formatting and use other colours e.g. for Champagne and Granache.

This is great for showing sales for individual wines. However, I also want to have a way of showing what the Grand Total is, particularly I want to show if it falls below £30,000,000 when I refresh the data or apply a filter. For this I can use Conditional Formatting on the Background or the Border of the visual.

2.3 Conditional Formatting for Backgrounds and Borders

As mentioned above, using Conditional Formatting on visual Backgrounds and or Borders is great for identifying targets involving Grand Totals. Also, as before, to find the Conditional Formatting options for Backgrounds and Borders, use the Ellipses.

I’m going to add the same Rule to both the Background and the Border of the column chart and also to a Card visual that shows the Grand Total Sales. This is the rule I’m using:

Key:-

1. Notice we’ve change this to “Format by Rules”.
2. The “Based on field” is usually the same field or measure that you want to format, in our case the measure “Total Sales”.However, you can base the formatting on values in a different column or measure, see “Using a Different Column or Measure as your “Based on field”.
3. Under Rules and after “If value”, you can select:-

is greater than or equal to
is greater than
is
Is blank – see below, Formatting Blank Values

These are what my visuals look like when my sales are above and below £30,000,000:-

3. Using the “Format by Field Value” Option

I don’t know whether you’ve noticed, but so far we’ve only been able to apply conditional formatting to numbers. So can you apply this formatting to text values? For example, how can I have the columns in our column chart coloured according to the wine name? Or in my Table Visual have each Wine name coloured differently? The answer is yes, you can do all of these things but for this we need to use the “Field Value” option on the “Format by” drop down.

What exactly does it mean by a “Field value”? Well, the “Field value” can indeed be a column or field that contains the names of colours, such as “Purple”, “PeachPuff”, “SkyBlue” or it can be a code equivalent as follows:-

– 3, 6 or 8-digit hex codes, for example #3E4AFF. Make sure you include the # symbol at the start of the code.

– RGB or RGBA values, like RGBA(234, 234, 234, 0.5).

– HSL or HSLA values, like HSLA(123, 75%, 75%, 0.5).

But “Field value” can also be a DAX Measure! (And here’s the real power to “Format by Field Value” – I’m often asked if you can use Measures in Power BI Conditional Formatting).

Let’s look firstly at using a column contain colour names or codes and then we’ll see how we can use DAX measures.

3.1. Using a Column in the Field Value

Let’s look first at using a column containing colour names. I created this DAX calculated column in the Wines table:-

Wine Colour = SWITCH ( Wines[WINE], "bordeaux","pink"""champagne","#fc7303", "chardonnay","yellow",

"malbec","red",

"grenache","purple",

"piesporter","cyan",

"chianti","brown",

"pinot grigio","skyblue",

"merlot","magenta",

"sauvignon blanc","peachpuff",

"rioja","green",

"chenin blanc","blue",

"shiraz", "rgb(75, 245, 66)" )

Now I’m going to use this DAX calculated column to format the Data colors of the Clustered Column chart visual and the WINE value in the Table Visual:-

Key:-

1. Notice we’ve change this to “Format by Field Value”
2. In the “Based on field” select the DAX calculated column. Because of the way things work in Power BI and for reasons not worth mentioning here, it will default to “First…”
3. And again just leave this as “First”.

These are what my visuals now look like based on the colours in the calculated column:-

3.2. Using a DAX Measure in the Field Value

Straightforward so far? Yes, but let’s now look at how creating DAX Measures can take this to another level. Here’s a scenario; I would like to use the colour “green” to identify all those wines that have sold more by Sales value in the current year compared to the last year.

Here are the 3 DAX Measures I’ve created:-

2018 sales = CALCULATE([Total Sales],DateTable[Year]=2018) 2019 sales = CALCULATE([Total Sales],DateTable[Year]=2019) Year comparison = if([2018 sales]>[2019 sales],"red")

So I can now use this DAX Measure “Year comparison” to format the Data colors in a Column Chart or to format the Background color of all three columns in a Table visual:-

Using “Field value” on a the Data colors of the Chart Visual

Using “Field value” on a Table Visual, in this case applied to “WINE”

4. Using Conditional Formatting to Create Dynamic Titles for Visuals

Someone, somewhere, needs to tell me how creating dynamic Visual Titles ever became part of “Conditional Formatting” but let that rest for the moment. There is no “formatting” involved.; no colours, no values, just the text you want to display in the Titles of your visuals. But as you’ll find this feature in the same way as you make “Bordeaux” formatted red, I suppose we just have to deal with it.

From the example below, you can see what we want to achieve. I’ve shown the source table for the data so you can see that our Wines each have a Supplier. I’ve a column chart visual that shows Total Sales by Wine and I’ve a slicer that filters the Wines by Supplier. In the example below, we’re looking at Wines supplied by Majestic and Redsky. You’ll notice this information is displayed in the Visual Title:-

Source Wines Table	Column Chart Visual with Slicer Selection Showing in Title
Image	Image

So to get to this point, we need to build up starting with easy methods to more complex ones. The example above uses a more complex method but as I said, let’s take it step by step.

4.1 Using a Column to Create the Title

As I always believe, let’s start with the basics; let’s just use the values from the field that is used by the slicer to populate the Title text, in this case “Supplier” is being used by the slicer. To do this, first find the Conditional formatting option on the Title text card. Hover over “Title text” and the ellipses appears where you can select Conditional formatting:-

So now these are the options I need to select:-

Key:-

1. Notice this set to “Format by Field Value”.
2. In the “Based on field” I’ve selected the Supplier column Because of the way things work in Power BI and for reasons not worth mentioning here, it will default to “First…”
3. And again just leave this as “First”.

So this is how my Visual Titles will display their text, using three different selections from the Slicer:-

One Supplier selected in Slicer	Multiple Suppliers selected in Slicer	No Suppliers selected in Slicer
Image	Image	Image

Clearly there is a problem that arises when anything other than a single selection in the Slicer. Of course this could easily be remedied by changing the “Selection Controls” of the Slicer to “Single Select”. The Slicer then uses radio buttons as opposed to check boxes and people are forced to make only a single selection in the Slicer:-

Note: Are you wondering why we keep getting “Alliance” when more than one Supplier has been selected in the Slicer? Well this is because the Summarization is set to “First”. This means that when multiple suppliers are selected in the Slicer or no Suppliers are selected, the “First” supplier found in the Wines table is “Alliance”, because it starts with “A”. Well of course!

If this isn’t going to work for you then I’m afraid to say, as always, the way forward is to resort to DAX. So below I’ve shown how using DAX measures you can truly control the text you want to display in the Title of your visual.

4.2 Using a DAX Measures to Create the Dynamic Title

The starting point is to remind ourselves of the source Table we are using, which is the Wines table, notice the WINE and SUPPLIER columns:-

Wines Table

So we’ll begin by ensuring that “Alliance” doesn’t show in the Title if multiple or no suppliers are chosen in the Slicer. This is the DAX measure that we’ll be using:-

Title Conditional Formatting = "You are showing " & SELECTEDVALUE ( Wines[SUPPLIER] , "Many Suppliers" )

In the Title Text Conditional Formatting options, I’ve used this Measure in the “Based on field”:

These are the results I get in the Title of the column chart depending on different Slicer selections:-

One Supplier selected in Slicer	Multiple Suppliers selected in Slicer	No Suppliers selected in Slicer
Image	Image	Image

Although we no longer get “Alliance” showing for multiple selections or no selections, we’ve still got problems when no selection has been made in the slicer (we’re haven’t filtered “Many Suppliers”). We could again use the “Single Selection” option for the slicer to ensure that people can only select a single supplier (see above). But what if this is not what we want?

Well yet again DAX comes to our rescue. Below are two DAX measures that solve the problem of multiple selections and no selection in the slicer.. The first measure lists in the Title of the Visual all values selected in the Slicer. The second measure takes account of many selections and uses “….and More” to account for more than two selections (although you many want to increase this to 3 or 4).

Title Conditional Formatting v2 = VAR NoFilteredSuppliers = COUNTROWS ( VALUES ( Wines[SUPPLIER] ) ) VAR NoAllSuppliers= COUNTROWS ( ALL ( Wines[SUPPLIER] ) ) RETURN IF ( NoAllSuppliers = NoFilteredSuppliers , "Sales by Wine", "Sales by Wine, filtered by " & CONCATENATEX ( VALUES ( Wines[SUPPLIER] ) , Wines[SUPPLIER] , " & ", Wines[SUPPLIER] , ASC ) )

This is what we now get if we use this measure in the “Based on Field” option of Conditional Formatting:-

One Supplier selected in Slicer	Multiple Suppliers selected in Slicer
Image	Image
No Suppliers selected in Slicer	Lots of Suppliers selected in Slicer
Image	Image

Now just for the icing on the cake! If you have many values in the slicer, would you want them all listed with “&” in between them? A bit of an overkill if you had more than four of five! I’ve decided on more than two suppliers selected as being the overkill point and edited the above measure as follows (in the yellow highlight).

Title Conditional Formatting v3 = VAR NoFilteredSuppliers = COUNTROWS ( VALUES ( Wines[SUPPLIER] ) ) VAR NoAllSuppliers= COUNTROWS ( ALL ( Wines[SUPPLIER] ) ) RETURN IF ( NoAllSuppliers = NoFilteredSuppliers , "Sales by Wine", "Sales by Wine, filtered by " & IF ( NoFilteredSuppliers > 2, CONCATENATEX ( TOPN ( 2, VALUES ( Wines[SUPPLIER] ),Wines[SUPPLIER] , ASC ), Wines[SUPPLIER], ", ", Wines[SUPPLIER],ASC) & ", and More…", CONCATENATEX ( VALUES ( Wines[SUPPLIER] ) , Wines[SUPPLIER] , " & ", Wines[SUPPLIER] , ASC ) )

So this is what I get if people select more than two suppliers:-

More than 2 Suppliers selected in Slicer

Using DAX measures such as the ones above and the Conditional formatting option on the Title card of the paint roller, you can have the Titles of your visuals dynanically display slicer selections.

So as I said at the beginning of this blog, quite a bit to this Conditional Formatting lark! I hope you get inspired to make your data come to life by using colours to “tell the story” and get your message across. Just to whet your appetites and give you some ideas, in the following section, I’ve shown you how I’ve used Conditional formatting on some of my favourite visuals from the Strictly Come Dancing report.

Putting it all Together

This Column Charts uses format by “Field Value” using a measure that colours the data grey if the Dance Score is less than the Average.

In these Table visuals the Average Dance score uses a color scale.

The Contestant column in the Top Scorers table uses “Field Value” to show winners in purple and finalists in green.

An icon is used to identify the current series contestants.

Color scales used in a Matrix to show contestants’ scores in each week.	An Icon identifies contestants who are in the Bottom Two for the first time.	Again using color scales to show high and low values.
Image	Image	Image

Benefit from expert training

At Burningsuit we have years of experience in providing expert, well-rated training courses in Power BI, DAX and Excel. We’re adept at providing the information and skills you need to make the most of Power BI together with your data and offer on-site and public courses across London and the UK.

To contact us, please call on 0800 0199 746 or use our Contact page. We’re always happy to discuss Power BI training and implementation.

Blog