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

On this publish I focus on an important facet of knowledge visualisation; Color Coding. I consider, color coding is one essentially 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 lots about issues. For example, we take a look at inexperienced grass, whether it is gentle 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 most likely too late.

One other good instance is visitors lights. When it’s inexperienced, everyone seems to be comfortable, when it’s yellow, everyone seems to be racing to go the junction, nicely, I’m simply kidding, some individuals are inclined to go the yellow gentle whereas everybody is aware of they should cease when visitors gentle is yellow proper?? And… when it’s purple, now we 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, nicely, we may color code from the day first that Energy BI born, however, maybe not in a means that I’m going to elucidate on this publish. Conditional formatting can be round for some time now. On this publish I present a method that we are able to 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 under:

• 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 (through Enter Information)
• We outline a variety of numbers to establish the ranges that our values will fall into. I personally use share, nevertheless it could be one thing else in your case
• We then outline some measures to choose a selected color for the measures we wish to color code

Producing HEX Colors On-line

There are numerous web sites on the market that may generate HEX colors for you. Listed here are my favorite ones:

• Chroma.js Shade Palette Helper: You possibly can merely choose the form 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 increase! It generates HEX values of the variety of colors you’d like to make use of in your report. A very highly effective characteristic in Chroma.js is that it experiments the color palette for being colourblind-safe.
• Color Gradient Desk Generator: Whereas it isn’t pretty much as good and as straightforward as Chroma.js to make use of, it has some cool options to supply totally free. What you must do is to enter begin and finish hexadecimal values of the colors you’d wish to create a gradient from. You additionally must 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 choose to check adjustments in my measures in share, 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 tasks I someday occur to make use of Color Gradient Desk Generator. As at all times, it will depend on 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 secure
• Now scroll down and replica 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 will likely be utilized in our conditional formatting. We title the desk “ConfigColour”. Comply with the steps under 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 share values for every color. By including these new columns we are able to color code the values as under:

That is good for color coding the circumstances 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 indicate the variety of incidents in Crimson.

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

Let’s add the 2 new columns as under:

• RankAsc: Accommodates share values ranging from 10% to 100% of which 10% represents darkish blue and 100% represents purple
• RankDsc: RankDsc quite the opposite accommodates share values ranging from 100% all the way down to 10% of which 100% represents darkish blue and 10% represents purple

We are able to 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 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 may write the scripts your self with none points:

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

However wait, there’s nonetheless a solution to ease your script writing through the use of the UI with integer values first and alter the code with integer values later.

We are able to use the identical methodology so as to add ” RankDsc” column as under:

= 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 take advantage of “AdventureWorksDW2017” pattern SQL server database. For the sake of this publish I solely import “FactInternetSales” and “DimDate” tables so my mannequin will seem like under:

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 12 months over 12 months
• Gross sales YTD YoY % : to calculate the share of gross sales adjustments 12 months over 12 months 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])
)

Word: 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

Thus far, 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. Once we use these measures in conditional formatting, they choose related colors for every knowledge level. We anticipate every datapoint to provide us a worth between 0 and 1 in order that after we format the worth as a share, then it might be between 0 and 100. However this can be 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 variety 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 usually are not essentially divisible by 10. For example, we could get 33% for a knowledge 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 system to show the values divisible by 10:

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

Right here is how the previous system works:

• We multiply the worth of Gross sales YoY % measure by 10 which supplies 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

Once we format the leads to share the ultimate share 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 may be reused in our report conserving 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 straightforward to make use of them to format the visuals conditionally (if supported).

The next visuals at the moment 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 “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
• Broaden “Information Color”
• Hover over “Default Color” then click on ellipsis button
• Click on “Conditional Formatting”
• Choose “Discipline 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 complete report I shared with you to strive:

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 study from you, so depart your feedback under.