Empower Your Story Telling Information Visualisation in Energy BI with Color Coding

This submit has been ready in my running a blog checklist for some time and now that is my final submit in 2019. I want you all have an exquisite yr forward.

On this submit I focus on an important side of information visualisation; Color Coding. I imagine, color coding is one essentially the most highly effective and environment friendly methods to offer correct data to the customers. We learnt as human being that the color can inform so much about issues. For example, we have a look at inexperienced grass, whether it is mild inexperienced we instantly perceive that the grass is sort of recent and wholesome. When she will get a bit yellowish, we all know that she’s maybe thirsty. When it will get brown it’s in all probability too late.

One other good instance is visitors lights. When it’s inexperienced, everyone seems to be joyful, when it’s yellow, everyone seems to be racing to move the junction, effectively, I’m simply kidding, some folks are inclined to move the yellow mild whereas everybody is aware of they must cease when visitors mild is yellow proper?? And… when it’s crimson, we now have to cease. Sufficient saying about color coding and its impacts on our lives on a daily foundation. Let’s discuss color coding in Energy BI and shortly get to extra thrilling stuff.

So… color coding in Energy BI, effectively, we might color code from the day first that Energy BI born, however, maybe not in a method that I’m going to clarify on this submit. Conditional formatting can be round for some time now. On this submit I present a way that we will implement in Energy BI to make use of a constant color coding throughout the entire report.

Here’s a report with out color coding:

And now look the identical report that’s color coded:

Let’s get into it.

Getting Began

On this approach we’ll comply with the steps beneath:

• We bounce on-line utilizing some superior free color palette web sites to generate the colors we’d like to make use of in our reviews
• We copy the HEX values and paste into Energy BI (by way of Enter Information)
• We outline a spread of numbers to establish the ranges that our values will fall into. I personally use proportion, nevertheless it could be one thing else in your case
• We then outline some measures to select a selected color for the measures we wish to color code

Producing HEX Colors On-line

There are a whole lot of web sites on the market that may generate HEX colors for you. Listed below are my favorite ones:

• Chroma.js Coloration Palette Helper: You possibly can merely choose the form of palette you’d prefer to generate (sequential or diverging) and in addition the variety of color you’d prefer to generate. You then merely choose two or three colors and increase! It generates HEX values of the variety of colors you’d like to make use of in your report. A extremely highly effective function in Chroma.js is that it experiments the color palette for being colourblind-safe.
• Color Gradient Desk Generator: Whereas it’s not nearly as good and as simple as Chroma.js to make use of, it has some cool options to supply free of charge. What you want to do is to enter begin and finish hexadecimal values of the colors you’d prefer to create a gradient from. You additionally must enter the variety of steps you’d prefer to generate the gradient from, then it shortly generates the values for you. One factor that I discovered fairly helpful is that it generated percentages for you, so in my case that I want to check adjustments in my measures in proportion, having percentages already generated comes helpful.

For the pattern report file I created for this weblog submit I used Chrome.js, however, in another actual world initiatives I someday occur to make use of Color Gradient Desk Generator. As at all times, it depends upon the case and what I’m going to do.

Let’s begin with producing some color palettes.

• Browse Chroma.js
• Click on “Diverging” for Palette sort
• Enter the variety of colors (I put 10)
• Enter or choose the start and ending colors by hovering over the colors
• Ensure that the color palette is colourblind protected
• Now scroll down and duplicate the HEX colors from the checklist

Color Coding in Energy BI

Let’s use the Hex colors in Energy BI. We have to paste the copied Hex codes in Energy BI Desktop then add some calculated columns that can be utilized in our conditional formatting. We identify the desk “ConfigColour”. Comply with the steps beneath to take action.

• Open Energy BI Desktop
• Click on “Enter Information” and paste the values then click on OK

• Now I wish to add an “Index” column which I’ll use in a second in different columns. TO add an index column click on “Index Column” drop down from “Add Column” tab then choose “From 1”

Now I’d like so as to add two different columns representing proportion values for every color. By including these new columns we will color code the values as beneath:

That is good for color coding the situations that get undesirable when the numbers develop, like when in a Well being and Security report, the variety of incidents grows in examine with the final interval. In that case I’d like to point out the variety of incidents in Purple.

Relying in your use case it’s possible you’ll wish to use the second column to help the other scenario, like when the variety of gross sales grows in examine with final interval gross sales. Then I’d like to point out the gross sales values in Darkish Blue to point a fascinating situation.

Let’s add the 2 new columns as beneath:

• RankAsc: Incorporates proportion values ranging from 10% to 100% of which 10% represents darkish blue and 100% represents crimson
• RankDsc: RankDsc quite the opposite comprises proportion values ranging from 100% right down to 10% of which 100% represents darkish blue and 10% represents crimson

We will add the 2 new columns utilizing “Index Column”, however this time we’d like decimal values as index. The present UI of “Add Index Column” does NOT permit decimal values:

The excellent news is that this a UI restriction solely and doesn’t apply to “Desk.AddIndexColumn” operate in Energy Question. Due to this fact, you may write the scripts your self with none points:

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

However wait, there may be nonetheless a approach to ease your script writing by utilizing the UI with integer values first and alter the code with integer values later.

We will use the identical methodology so as to add ” RankDsc” column as beneath:

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

Now change the info sort of the ” RankAsc” and ” RankDsc” columns to “Proportion”.

Creating Information Mannequin in Energy BI Desktop

It’s now time to connect with your knowledge supply and construct your knowledge mannequin in Energy BI. I exploit “AdventureWorksDW2017” pattern SQL server database. For the sake of this submit I solely import “FactInternetSales” and “DimDate” tables so my mannequin will seem like beneath:

With the next relationships:

Now I wish to create the next two measures and color code the outcomes:

• Gross sales YoY % : to calculate the share of gross sales adjustments yr over yr
• Gross sales YTD YoY % : to calculate the share of gross sales adjustments yr over yr thus far

Right here is the measure dependencies and DAX expressions used within the above measures:

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

Gross sales Variance Present 12 months vs Final 12 months = 
var firstDateLYTD = FIRSTNONBLANK('Date'[FullDateAlternateKey], [Sales Last Year])
return
CALCULATE([Total Sales Amount] - [Sales Last Year], firstDateLYTD)

Gross sales Final 12 months = 
	CALCULATE(
		[Total Sales Amount],
		CALCULATETABLE(DATEADD('Date'[FullDateAlternateKey], -1, YEAR)
                    , 'Date'[IsValidDateYTD] = TRUE()
	))

Complete Gross sales Quantity = SUM('Web Gross sales'[SalesAmount])

Date.IsValidDateYTD = 
AND('Date'[DateKey] >= MIN('Web Gross sales'[OrderDateKey])
, 'Date'[DateKey] <= MAX('Web Gross sales'[OrderDateKey])
)

Be aware: The latter “Date.IsValidDateYTD” is a calculated column created in Date desk to establish legitimate dates primarily based on Order Date.

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

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

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

Creating Formatting Measures

To date, we’ve created our knowledge mannequin in addition to a Color Config desk. To have the ability to use the colors imported to the “ColourConfig” desk in our conditional formatting, we have to create some textual measures. After we use these measures in conditional formatting, they decide related colors for every knowledge level. We count on every datapoint to offer us a worth between 0 and 1 in order that once we format the worth as a proportion, then it will be between 0 and 100. However this can be a bit difficult because the values we now have in both RankAsc or RandDsc columns are divisible by 10, in different phrases, every worth represents a spread of values or a bucket of values. For instance, 30% means any values between 30% and 39% falls into the 30% bucket. Now the problem is that the outcomes of the Gross sales YoY % measure should not essentially divisible by 10. For example, we might get 33% for an information level, however 33% doesn’t exist in both RankAsc or RankDsc columns. After we eyeball the info, we shortly perceive that 33% falls into the 30% bucket. To calculate the identical logic in DAX want use the next formulation to show the values divisible by 10:

CONVERT([Sales YoY %] * 10, INTEGER)/10

Right here is how the previous formulation works:

• We multiply the worth of Gross sales YoY % measure by 10 which provides us a decimal worth between 0 and 10 (we are going to cope with the conditions when the worth is smaller than 0 or greater than 10)
• We convert the decimal worth to an integer to drop the digits after the decimal level.
• Eventually, we divide the worth by 10

After we format the ends in proportion the ultimate proportion worth is divisible by 10. We then verify if the worth is smaller than 10% we return 10% and whether it is greater than 100% we return 100%.

Now we create two textual measures, one to select the Hex color and one other one to select the outline. The measures could be reused in our report holding our color coding constant throughout the report. So I created the next measures, it’s possible you’ll create extra measures in a real-world situation.

Gross sales YoY % Color = 
var percentRound = CONVERT([Sales YoY %] * 10, INTEGER)/10
var checkMinValue = IF(percentRound < 0.1, 0.1, percentRound)
var checkMaxValue = IF(checkMinValue > 1, 1, checkMinValue)
return
CALCULATE(
    VALUES(ConfigColour[ColourHex])
    , FILTER( ConfigColour
        , 'ConfigColour'[RankDsc] = checkMaxValue
        )
    )

Gross sales YoY % Textual content = 
var percentRound = CONVERT([Sales YoY %] * 10, INTEGER)/10
var checkMinValue = IF(percentRound < 0.1, 0.1, percentRound)
var checkMaxValue = IF(checkMinValue > 1, 1, checkMinValue)
return
CALCULATE(
    VALUES(ConfigColour[RankDsc Group])
    , FILTER( ConfigColour
        , 'ConfigColour'[RankDsc] = checkMaxValue
        )
    )

Utilizing Formatting Measures in Conditional Formatting

Now that we created some formatting measures it’s tremendous simple to make use of them to format the visuals conditionally (if supported).

The next visuals at present help 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 “Information Label” color or “Background” color
• KPI
• Desk
• Matrix

On this part I clarify how simply you may conditionally format clustered column chart.

• Place a Clustered Column Chart on a web page in Energy BI Desktop
• Put “12 months” and “Month” on Axis
• Put “Gross sales YoY %” on Worth
• Click on “Format” tab from “Visualisation” pane
• Increase “Information Color”
• Hover over “Default Color” then click on ellipsis button
• Click on “Conditional Formatting”
• Choose “Area Worth” from “Fromat by” drop down checklist
• Choose “Gross sales YoY % Color” measure then click on OK

That is what we get:

Right here is the total report I shared with you to attempt:

You possibly can obtain the PBIX file right here.

Have you ever used this method? Are you utilizing different methods? I’d love to listen to and be taught from you, so go away your feedback beneath.

Associated