Data Bear – Power BI Training and Consulting

Call us Today! (020) 8720 6880 | info@databear.com

Changing colours using DAX and conditional formatting in Power BI

Changing colours using DAX and conditional formatting in Power BI

In this post, you will learn how to use DAX and conditional formatting for dynamically changing colours in visuals to highlight the highest and lowest values.

Power BI developers have added Conditional Formatting to nearly all their features and this truly ups the game for all Front-end report developers.  These details enhance the user experience tenfold.

Your report can now be smart and fully dynamic by changing colours and thus making it easier for your users to find insights quicker.

With only these 3 quick and easy steps you can achieve this.

Below you can see this in action, see how the highest and lowest changes as the Sub Category filter is clicked.

Changing colours bar chart with slicer interaction

 

Steps to dynamically changing colours

Step 1:

Create a new measure to sum the values that are displayed in the graph.

Sales rev = sum(Salestable[Revenue])

 

Step 2: 

Create a new measure to determine the highest and lowest values for the category on the X-axis.  That being the Month in this case.  Then use an IF function to allocate the correct colour with hex codes.

Highest / Lowest Value =

var highestvalue = MAXX(ALLSELECTED(Salestable[End of Month]),[Sales rev])

var lowestvalue = MINX(ALLSELECTED(Salestable[End of Month]),[Sales rev])

return

IF([Sales rev] = highestvalue,”#73B761″,

IF([Sales rev] = lowestvalue,”#DB271C”)

 

Step 3:

Set up Conditional Formatting

Go to the Format settings of the chart

Format visual

 

 

 

Go to Data colours and click on fx button

Data Colors formatting

 

 

 

On the Conditional formatting screen under “Format by”,  choose Field Value.

Under “Based on field”, navigate to the measure created in step 2.

Conditional Formatting Settings

 

 

 

 

 

And…… voila!! Magic happens.

 

Another way to use this

Alternatively, you can switch this around, to high light the highest and lowest quantity values rather than revenue.

Step 1:

Quantity = sum(Salestable[Quantity])

 

Step 2:

Highest / Lowest Value (subcategory) =

var highestvalue = MAXX(ALLSELECTED(Salestable[Sub Category]),[Quantity])

var lowestvalue = MINX(ALLSELECTED(Salestable[Sub Category]]),[Quantity])

return

IF([Quantity]= highestvalue,”#73B761″,

IF([Quantity]= lowestvalue,”#DB271C”)

 

Step 3:

Follow above step 3, but with the new measure.

Although it is advised to order bar charts from the biggest value to the smallest, if its categorical data, sometimes it is just better to have the categories alphabetical.  That is when having Dynamically changing colours makes sense, simply because it’s not always that easy to see which category has the highest value.

Changing colours bar chart with slicer interaction

 

You can take it one step further by adding the custom visual, Play axis, to run through the months to see how or if the best sellers keep performing.  For decision-makers, this could be a time saver.

Play Axis Slicer in action

 

Another option is having your chart showing revenue, but using the dynamic colour changing to show quantity, that way your user can determine if revenue and quantity coincide.

This is such a simple way to elevate your charts to the next level.  This can be incorporated in many ways and different visuals like tables, funnels charts even treemaps and pie charts.

Don’t be scared to try new things, that’s why undo and don’t save was invented.

This post is the first of many I will be sharing with you as a new member of the Data Bear team.  I will be sharing frequent “How to” posts with my tips on creating amazing reports, dashboards and charts using Power BI. The content I share will be my personal experiences from using Power BI over the last 2.5 years. Data Analysis and Data Visualization is a passion and I love sharing it with others.

Additional Information

Definitions for DAX used in this post

MAXX  = Evaluates an expression for each row of a table and returns the largest value.

ALLSELECTED = Removes context filters from columns and rows in the current query, while retaining all other context filters or explicit filters.  The ALLSELECTED function gets the context that represents all rows and columns in the query, while keeping explicit filters and contexts other than row and column filters. This function can be used to obtain visual totals in queries.

If you would like to learn more about Power BI, join our training course.

Leave a Comment

Your email address will not be published. Required fields are marked *