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.

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

Go to Data colours and click on fx button

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

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

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.

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.

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 (with link to https://docs.microsoft.com/en-us/dax/maxx-function-dax )  = Evaluates an expression for each row of a table and returns the largest value.

ALLSELECTED (with link to https://docs.microsoft.com/en-us/dax/allselected-function-dax ) = 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.