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:
And now look the same report that is colour coded:
Let’s get into it.
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:
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.
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:
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)
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.
We can use the same method to add ” RankDsc” column as below:
= Table.AddIndexColumn(#"Added RankAsc", "RankDsc", 1, -0.1)
Now change the data type of the ” RankAsc” and ” RankDsc” columns 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
- Card: from “Data Label” colour or “Background” colour
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
This is what we get:
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.