In this blog, I will show you how to dynamically change the color of a bar or column chart based on conditions using DAX Measure.
For example, let’s suppose that if my sales are less than 500, I want the bar color to be red. If the sales are greater than 500, then the color should be orange, otherwise, it should be a different color. This means I want to change colors dynamically based on the data.
Generally, we set bar or column chart colors manually, but in this blog, I will show you how to apply conditional formatting to achieve this automatically.
As you can see in the screenshot below, I have a clustered column chart displaying category-wise sales, and all category sales columns have the same color.
If you check the formatting options for this chart, you will notice that you can only manually change the colors for each category.
We can achieve this using conditional formatting with rules, but my requirement is to change the colors dynamically based on sales using a DAX measure.
To achieve this, we need to write a DAX measure and then use it in conditional formatting to control the colors of the visual.
Now, let’s start the practical. First, you need to create a measure by right-clicking on the dataset name and selecting “New Measure.” After that, write the following DAX code.
Column color code = SWITCH( TRUE(), SUM(Sales[Sales])<= 500, "#FF0000", --Red SUM(Sales[Sales]) > 500 && SUM(Sales[Sales]) <= 3000, "#FBB03B",-- Orange SUM(Sales[Sales]) > 3000, "#2C8838", --Green "#666666"--Grey for Deafult )
Now, select the chart and go to “Format Visual.” Under the Columns section, make sure “All” is selected in the Categories dropdown, then click on the Fx icon.
The Color Categories pop-up window will open. Then, select “Field value” in the Format style dropdown. Next, expand your dataset in the other dropdown, choose the measure, and click OK.
As you can see in the screenshot below, the chart colors have changed according to the defined conditions and color codes.
Thanks for reading this post! I hope you found it helpful. Feel free to share it with others or your teammates so they can benefit from it too. 😊
