This post has been waiting in my blogging list for a while and now this is my last post in 2019. I wish you all have a wonderful year ahead.

In this post I discuss a very important aspect of data visualisation; Colour Coding. I believe, colour coding is one the most powerful and efficient ways to provide proper information to the users. We learnt as human being that the colour can tell a lot about things. For instance, we look at green grass, if it is light green we immediately understand that the grass is quite fresh and healthy. When she gets a bit yellowish, we know that she’s perhaps thirsty. When it gets brown it is probably too late.

Another perfect example is traffic lights. When it is green, everyone is happy, when it is yellow, everyone is racing to pass the junction, well, I’m just kidding, some people tend to pass the yellow light while everyone knows they have to stop when traffic light is yellow right?? And… when it is red, we have to stop. Enough saying about colour coding and its affects on our lives on a day to day basis. Let’s talk about colour coding in Power BI and quickly get to more exciting stuff.

So… colour coding in Power BI, well, we could colour code from the day first that Power BI born, but, perhaps not in a way that I’m going to explain in this post. Conditional formatting is also around for a while now. In this post I show a technique that we can implement in Power BI to use a consistent colour coding across the whole report.

Here is a report without colour coding:

Power BI Report without Colour Coding

And now look the same report that is colour coded:

Colour Coded Power BI Report

Let’s get into it.

Getting Started

In this technique we’ll follow the steps below:

We jump online using some awesome free colour palette websites to generate the colours we’d like to use in our reports

We copy the HEX values and paste into Power BI (via Enter Data)

We define a range of numbers to identify the ranges that our values will fall into. I personally use percentage, but it might be something else in your case

We then define some measures to pick a specific colour for the measures we want to colour code

Generating HEX Colours Online

There are a lot of websites out there that can generate HEX colours for you. Here are my favourite ones:

Chroma.js Color Palette Helper: You can simply select the kind of palette you’d like to generate (sequential or diverging) and also the number of colour you’d like to generate. You then simply select two or three colours and boom! It generates HEX values of the number of colours you’d like to use in your report. A really powerful feature in Chroma.js is that it experiments the colour palette for being colourblind-safe.

Colour Gradient Table Generator: While it is not as good and as easy as Chroma.js to use, it has some cool features to offer for free. What you need to do is to enter start and end hexadecimal values of the colours you’d like to create a gradient from. You also need to enter the number of steps you’d like to generate the gradient from, then it quickly generates the values for you. One thing that I found quite useful is that it generated percentages for you, so in my case that I prefer to compare changes in my measures in percentage, having percentages already generated comes handy.

For the sample report file I created for this blog post I used Chrome.js, but, in some other real world projects I sometime happen to use Colour Gradient Table Generator. As always, it depends on the case and what I’m going to do.

Let’s start with generating some colour palettes.

Browse Chroma.js

Click “Diverging” for Palette type

Enter the number of colours (I put 10)

Enter or select the beginning and ending colours by hovering over the colours

Make sure the colour palette is colourblind safe

Now scroll down and copy the HEX colours from the list

Colour Coding in Power BI

Let’s use the Hex colours in Power BI. We need to paste the copied Hex codes in Power BI Desktop then add some calculated columns that will be used in our conditional formatting. We name the table “ConfigColour”. Follow the steps below to do so.

Open Power BI Desktop

Click “Enter Data” and paste the values then click OK

Now I want to add an “Index” column which I’ll use in a moment in other columns. TO add an index column click “Index Column” drop down from “Add Column” tab then select “From 1”

Now I’d like to add two other columns representing percentage values for each colour. By adding these new columns we can colour code the values as below:

Colour coding undesirable conditions in Red and the desirable ones in Dark Blue.

This is good for colour coding the conditions that get undesirable when the numbers grow, like when in a Health and Safety report, the number of incidents grows in compare with the last period. In that case I’d like to show the number of incidents in Red.

Depending on your use case you may want to use the second column to support the opposite situation, like when the number of sales grows in compare with last period sales. Then I’d like to show the sales values in Dark Blue to indicate a desirable condition.

Colour coding undesirable conditions in Dark Blue and the desirable ones in Red .

Let’s add the two new columns as below:

RankAsc: Contains percentage values starting from 10% to 100% of which 10% represents dark blue and 100% represents red

RankDsc: RankDsc on the contrary contains percentage values starting from 100% down to 10% of which 100% represents dark blue and 10% represents red

We can add the two new columns using “Index Column”, but this time we need decimal values as index. The current UI of “Add Index Column” does NOT allow decimal values:

Add index column UI doesn’t allow decimal values

The good news is that this a UI restriction only and doesn’t apply to “Table.AddIndexColumn” function in Power Query. Therefore, you can write the scripts yourself without any issues:

= Table.AddIndexColumn(#"Added Index", "RankAsc", 0.1, 0.1)

Using Table.AddIndexColumn function in Power Query with decimal values

But wait, there is still a way to ease your script writing by using the UI with integer values first and change the code with integer values later.

Add Index Column with with decimal values

We can use the same method to add ” RankDsc” column as below:

= Table.AddIndexColumn(#"Added RankAsc", "RankDsc", 1, -0.1)

Using Table.AddIndexColumn function in Power Query with descending decimal values

Now change the data type of the ” RankAsc” and ” RankDsc” columns to “Percentage”.

Changing Columns Data Types to Percentage

Creating Data Model in Power BI Desktop

It is now time to connect to your data source and build your data model in Power BI. I use “AdventureWorksDW2017” sample SQL server database. For the sake of this post I only import “FactInternetSales” and “DimDate” tables so my model will look like below:

With the following relationships:

Now I want to create the following two measures and colour code the results:

Sales YoY % : to calculate the percentage of sales changes year over year

Sales YTD YoY % : to calculate the percentage of sales changes year over year to date

Here is the measure dependencies and DAX expressions used in the above measures:

Sales YoY % = DIVIDE([Sales Variance Current Year vs Last Year], [Sales Last Year])

Sales Variance Current Year vs Last Year = var firstDateLYTD = FIRSTNONBLANK('Date'[FullDateAlternateKey], [Sales Last Year]) return CALCULATE([Total Sales Amount] - [Sales Last Year], firstDateLYTD)

Sales Last Year = CALCULATE( [Total Sales Amount], CALCULATETABLE(DATEADD('Date'[FullDateAlternateKey], -1, YEAR) , 'Date'[IsValidDateYTD] = TRUE() ))

Total Sales Amount = SUM('Internet Sales'[SalesAmount])

Date.IsValidDateYTD = AND('Date'[DateKey] >= MIN('Internet Sales'[OrderDateKey]) , 'Date'[DateKey] <= MAX('Internet Sales'[OrderDateKey]) )

Note: The latter “Date.IsValidDateYTD” is a calculated column created in Date table to identify valid dates based on Order Date.

Sales YTD YoY % = DIVIDE([Sales YTD] - [Sales LYTD], [Sales LYTD])

Sales YTD = CALCULATE([Total Sales Amount] , CALCULATETABLE(DATESYTD('Date'[FullDateAlternateKey]) , 'Date'[IsValidDateYTD]) )

Sales LYTD = CALCULATE([Total Sales Amount] , CALCULATETABLE(DATESYTD(DATEADD('Date'[FullDateAlternateKey], -1, YEAR)) , 'Date'[IsValidDateYTD]) )

Creating Formatting Measures

So far we’ve created our data model as well as a Colour Config table. To be able to use the colours imported to the “ColourConfig” table in our conditional formatting we need to create some measures. When we use those measures in conditional formatting they pick relevant colours for each data point. The measures can be reused in our report keeping our colour coding consistent across the report. So I created the following measures, you may create more measures in a real world scenario.

MAX Hex Colour from Index = MAXX( FILTER( ALL('ConfigColour'[ColourHex], 'ConfigColour'[Index]) , 'ConfigColour'[Index] = MAX('ConfigColour'[Index]) ) , 'ConfigColour'[ColourHex] )

MIN Hex Colour from Index = MINX( FILTER( ALL('ConfigColour'[ColourHex], 'ConfigColour'[Index]) , 'ConfigColour'[Index] = MIN('ConfigColour'[Index]) ) , 'ConfigColour'[ColourHex] )

Sales YoY % Colour = SWITCH ( TRUE (), ISBLANK([Sales YoY %]), BLANK(), [Sales YoY %] < 0.005 , [MAX Hex Colour from Index], [Sales YoY %] > 1 , [MIN Hex Colour from Index], MAXX ( FILTER ( ALL('ConfigColour'[RankDsc], 'ConfigColour'[ColourHex]) , 'ConfigColour'[RankDsc] = ROUND(CONVERT([Sales YoY %] * 10, double), 1) ), 'ConfigColour'[ColourHex] ) )

Sales YTD YoY% Colour = SWITCH ( TRUE (), ISBLANK([Sales YTD YoY %]), BLANK(), [Sales YTD YoY %] < 0.1 , [MAX Hex Colour from Index], [Sales YTD YoY %] > 1 , [MIN Hex Colour from Index], MAXX( FILTER ( ALL('ConfigColour'[ColourHex], 'ConfigColour'[RankDsc]) , 'ConfigColour'[RankDsc] = ROUND ( [Sales YTD YoY %], 1 ) ), 'ConfigColour'[ColourHex] ) )

Using Formatting Measures in Conditional Formatting

Now that we created some formatting measures it is super easy to use them to format the visuals conditionally (if supported).

The following visuals currently support conditional formatting:

Stacked Bar Chart

Stacked Column Chart

Clustered Bar Chart

Clustered Column Chart

100% Stacked Bar Chart

100% Stacked Column Chart

Line and Stacked Column Chart

Line and Clustered Column Chart

Ribbon Chart

Funnel Chart

Scatter Chart

Treemap Chart

Gauge

Card: from “Data Label” colour or “Background” colour

KPI

Table

Matrix

In this section I explain how easily you can conditionally format clustered column chart.

Place a Clustered Column Chart on a page in Power BI Desktop

Put “Year” and “Month” on Axis

Put “Sales YoY %” on Value

Click “Format” tab from “Visualisation” pane

Expand “Data Colour”

Hover over “Default Colour” then click ellipsis button

Click “Conditional Formatting”

Select “Field Value” from “Fromat by” drop down list

Select “Sales YoY % Colour” measure then click OK

Conditional Formatting in Clustered Column Chart

This is what we get:

Conditionally Formatted Clustered Column Chart

Here is the full report I shared with you to try:

You can download the PBIX file here.

Have you used this technique? Are you using other techniques? I’d love to hear and learn from you, so leave your comments below.

Like this: Like Loading...