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

On this publish I focus on an important side of knowledge visualisation; Color Coding. I consider, color coding is one probably the most highly effective and environment friendly methods to offer correct info to the customers. We learnt as human being that the color can inform loads about issues. For example, we have a look at inexperienced grass, whether it is gentle inexperienced we instantly perceive that the grass is kind of contemporary 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 excellent instance is site visitors lights. When it’s inexperienced, everyone seems to be joyful, when it’s yellow, everyone seems to be racing to cross the junction, properly, I’m simply kidding, some folks are inclined to cross the yellow gentle whereas everybody is aware of they must cease when site visitors gentle is yellow proper?? And… when it’s crimson, now we have to cease. Sufficient saying about color coding and its impacts on our lives on a day after day foundation. Let’s discuss color coding in Energy BI and shortly get to extra thrilling stuff.

So… color coding in Energy BI, properly, 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 publish. Conditional formatting can also be round for some time now. On this publish 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 leap on-line utilizing some superior free color palette web sites to generate the colors we’d like to make use of in our studies
• We copy the HEX values and paste into Energy BI (by way of Enter Knowledge)
• We outline a spread of numbers to establish the ranges that our values will fall into. I personally use proportion, however it could be one thing else in your case
• We then outline some measures to choose a particular color for the measures we need to color code

Producing HEX Colors On-line

There are quite a 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’ll be able to merely choose the type of palette you’d wish to generate (sequential or diverging) and in addition the variety of color you’d wish to generate. You then merely choose two or three colors and growth! It generates HEX values of the variety of colors you’d like to make use of in your report. A extremely highly effective characteristic 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 without spending a dime. What that you must do is to enter begin and finish hexadecimal values of the colors you’d wish to create a gradient from. You additionally have to enter the variety of steps you’d wish 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 desire to match modifications in my measures in proportion, having percentages already generated comes helpful.

For the pattern report file I created for this weblog publish I used Chrome.js, however, in another actual world initiatives I someday occur to make use of Color Gradient Desk Generator. As all the time, it is determined by 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
• Be sure the color palette is colourblind protected
• Now scroll down and duplicate the HEX colors from the record

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 title the desk “ConfigColour”. Observe the steps beneath to take action.

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

• Now I need 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 evaluate with the final interval. In that case I’d like to point out the variety of incidents in Crimson.

Relying in your use case you might need to use the second column to assist the other state of affairs, like when the variety of gross sales grows in evaluate 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: Accommodates proportion values ranging from 10% to 100% of which 10% represents darkish blue and 100% represents crimson
• RankDsc: RankDsc quite the opposite incorporates 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 want decimal values as index. The present UI of “Add Index Column” does NOT enable decimal values:

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

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

However wait, there’s nonetheless a option 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 technique 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 Knowledge Mannequin in Energy BI Desktop

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

With the next relationships:

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

• Gross sales YoY % : to calculate the share of gross sales modifications yr over yr
• Gross sales YTD YoY % : to calculate the share of gross sales modifications yr over yr to this point

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()
	))

Whole 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])
)

Observe: The latter “Date.IsValidDateYTD” is a calculated column created in Date desk to establish legitimate dates based mostly 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 this point, we’ve created our information 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. Once we use these measures in conditional formatting, they decide related colors for every information level. We anticipate every datapoint to provide us a worth between 0 and 1 in order that once we format the worth as a proportion, then it might be between 0 and 100. However it is a bit difficult because the values now we 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 could get 33% for an information level, however 33% doesn’t exist in both RankAsc or RankDsc columns. Once 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 method to show the values divisible by 10:

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

Right here is how the previous method 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.
• Ultimately, we divide the worth by 10

Once 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 choose the Hex color and one other one to choose the outline. The measures might be reused in our report preserving our color coding constant throughout the report. So I created the next measures, you might create extra measures in a real-world state of affairs.

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 assist 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 “Knowledge Label” color or “Background” color
• KPI
• Desk
• Matrix

On this part I clarify how simply you possibly can 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
• Broaden “Knowledge Color”
• Hover over “Default Color” then click on ellipsis button
• Click on “Conditional Formatting”
• Choose “Subject Worth” from “Fromat by” drop down record
• 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’ll be able to obtain the PBIX file right here.

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