3 easy steps to use SWITCH function to make a conditional coloured bar charts in Power BI
Power BI is a platform that allows you to connect to various data sources, transform and model your data, and create interactive dashboards and reports. Power BI can help you gain insights from your data and communicate them effectively.
What are Bar Charts or Graphs?
Bar graphs, also known as bar charts, are a type of data visualization that represent data in the form of vertical or horizontal rectangular bars. The length of the bars is proportional to the measure of the data they represent. Bar graphs are a powerful tool in statistics for the presentation, analysis and interpretation of data. They make it easy to compare data and understand trends.
Building the Chart
We will be making this Chart:
In this chart we are taking customer_region which is a categorical data in Y- axis and YoY Sales in X- axis and if YoY Sales>0 then we are assigning Green colour to bars, if YoY Sales<0 then we are assigning Red colour to bars and if YoY Sales = 0 then Blue colour.
1.Creating the Bar Chart:
First we need to create a bar chart for which we head to Visualizations and in Build visual select Stacked bar chart. Now select the chart and in Y-axis put customer_region and in X-axis put YoY Sales.
Now go to Format visual then head to Visual, turn on Data labels and set Position to Outside end.
After doing all above the chart should look like this
2.DAX to assign conditional colours to bar charts:
Colours = SWITCH(TRUE(), [YoY Sales]>0, “Greater”, [YoY Sales]<0, “Less”, [YoY Sales]=0, “No Sales”)
TRUE()is the expression thatSWITCHwill evaluate. In this case, it’s alwaysTRUE, soSWITCHwill check each subsequent condition in order.[YoY Sales]>0, "Greater": If theYoY Sales(Year-over-Year Sales) is greater than 0, the formula will return “Greater”.[YoY Sales]<0, "Less": If theYoY Salesis less than 0, the formula will return “Less”.[YoY Sales]=0, "No Sales": If theYoY Salesis equal to 0, the formula will return “No Sales”.
So, this formula is creating a new column Colours that categorizes the YoY Sales into “Greater”, “Less” or “No Sales” based on whether the sales are positive, negative or zero.
3.Assigning the Colours measure to the bar chart:
Now click on the chart go to Format visual expand Bars and in Color press fx.
In Format style choose Rules and in field select Colours . Now in Rules type Greater in the same way as you wrote in the IF function of the Colours following the if values is portion and choose green colour then add another rule by pressing +New rule and type Less following the if values is portion and again in the same way as in IF function then just choose red colour then add another rule by pressing +New rule and type No Sales following the if values is portion and choose blue colour.
Press OK and we have our chart.
In similar ways you can assign colours to other charts just try it out.
To go to Part 1 of the Professional KPI series 2 refer to this link.
To create a dynamic KPI in Power BI refer to this link.
To make a dynamic measure column refer to this link.
To make Professional KPI refer to this link.
To go to Part 1 of the Professional KPI series 1 refer to this link.
To read more stories like this you can follow me with this link.
Don’t forget to subscribe to
and join our Power BI community
