Recently, while working on a report, I was required to show multiple values by grouping them together. I researched this and found that a Power BI clustered bar chart is the best option. Even while doing a little more research, I found out that we can also do a lot of formatting in it, like displaying percentage values, overlapping, displaying multiple values, etc.
Power BI Clustered Bar Chart
A clustered bar chart in Power BI is a type of horizontal chart where multiple bars are grouped together horizontally under the same y-axis values, forming clusters.
For example, if you have a product category, sales, and profit columns, you can show Sales and Profit by Product Category in the Power BI Clustered Bar Chart, as shown in the screenshot below.
Create a Clustered Bar Chart in Power BI
In this example, I use the sales table data (Sales.xlsx) below to create a clustered bar chart in Power BI. You can create a similar Excel file and then follow the steps.
Using this data, I will show sales and profit based on Product Category in a Clustered bar chart.
- Open Power BI Desktop and load the data from the data source using the Get Data option.
- Then select the Clustered bar chart from the Visualizations pane, and you can see the blank visual created in Report view.
- Next, drag and drop the Product Category to the Y-axis and the Sales and Profit to the X-axis.
- You can see a cluster bar chart displaying sales and Profits based on product category.
This is how to create a cluster bar chart in Power BI.
Now, let me show you some formatting options in the clustered bar chart.
Sort Clustered Bar Chart in the Power BI
The first formatting option I will show you is how to sort the clusters bar in the Power BI Clustered bar chart.
For example, we will take the above Power BI clustered bar chart and sort the X-axis in ascending order.
- To do this, select the clustered bar chart in Power BI Canvas and click on More options (…).
- Then select Sort axis -> Product Category -> Sort ascending option.
Now, you can see the clustered bar chart, X-axis, i.e., the Product Category is sorted in Ascending order.
This way, you can sort sales and profit values in ascending and descending order.
Increase Bar Width in Power BI Clustered Bar Chart
While modifying the clustered bar chart according to my requirements, I found that we could increase the bar width with the help of a slider. If you want to increase the bar width. Let’s see how we can achieve this:
- Select the Cluster bar chart, click the Format pane, and expand the Bars section. Select the series as ‘ALL‘.
- Then, expand the Layout section here and increase the slider under Space between categories.
- Then, the bar width increases in the Power BI Clustered bar chart.
We can use Power BI clustered bar charts to increase bar width.
Power BI Clustered Bar Chart Overlap
Let me show you another interesting formatting option: overlapping. Yes, you heard right. We can overlap the bars on top of each other. To do this:
- Select the Cluster bar chart, click the Format pane, and expand the bar section. Select the series as ‘ALL‘.
- Then, expand the Layout section and toggle on the Overlap option.
- Next, slide the ‘Space between series‘ slider. You can see the bars overlap with each other.
If you want to flip the overlap, the blue color will be on top of the navy blue. In that case, you need to turn on the Flip overlap option. You can see something like this below.
This is how we can create an overlapping cluster bar chart in Power BI.
Add Space Between Bars in a Power BI Clustered Bar Chart
Here, I will show how we add space between the Sales and profit bars in the Power BI Clustered bar chart.
- Select the Cluster bar chart, click the Format pane, and expand the bar section. Select the series as ‘ALL’.
- Then, expand the Layout section and increase the slider under Space between the categories.
- We also added space for two bars, such as sales and profit, using space between series.
- You can see the space between the clustered bar in the screenshot below.
These things help you if you have multiple values in the Power BI Clustered bar chart.
Display Multiple Values in a Clustered Bar Chart in Power BI
Sometimes, we may need to add multiple values in a clustered bar chart. For example, I want to show Sales, Profit, and COGS based on Product Name.
- Select the Clustered bar chart from the Visualizations pane in Power BI Desktop.
- Then, drag and drop the Product name to the X-axis and Sales, Profit, and COGS to the Y-axis.
- Then, the cluster bar chart shows sales, profit, and COGS by product name.
This way, you can add multiple values in the Power BI Clustered bar chart, but if you do, it is difficult to see all the bars, so try to avoid this.
Show Percentage in Clustered Bar Chart in Power BI
Another interesting thing is that I can show the percentage of the grand total in the bars of a Power BI clustered bar chart. To do this:
- Select the Clustered bar chart from the visualization pane in Power BI Desktop.
- Next, drag and drop the Product Category to the Y-axis and the Sales and Profit to the X-axis.
- You can see a cluster bar chart displaying sales and Profits based on product category.
Then select the drop-down next to ‘Sum of sales‘ and select ‘Show value as. ‘ Then select Percentage of grand total.
Then, you can see the percent in the Power BI Cluster bar chart.
This is an example of the percentage in a Clustered bar chart in Power BI.
Apply Conditional Formatting in Power BI Clustered Bar Chart
Here, we will see how to change the bar’s color based on sales value in the Power BI Clustered Bar Chart.
Unfortunately, conditional formatting cannot be applied in the Power BI Clustered bar chart when the x-axis contains more than one value.
If your clustered bar chart has one value like the bar chart, then you can apply conditional formatting following the below steps:
- We use the same data set as the above sales Excel file.
- Select the Clustered bar chart from the Visualizations pane.
- Then drag and drop the Product name into the Y-axis and sales into the X-axis.
- Next, click the Format icon in the Visualizations pane, then expand the Bars section.
- Click on the fx in the color section.
- The Conditional Formatting window will open. Provide the following information:
- Select the Format style as a Gradient.
- Choose the ‘What field should we base this on ‘as the sum of Sales.
- Choose the ‘Summarization‘ as Sum.
- Next, ‘How should we format empty values‘ as ‘As zero‘.
- Then, in Minimum value, select Custom from the dropdown and enter the value as 400. Select the color.
- For the Maximum value, select Custom from the dropdown and enter 1000. Then, select the color.
- You can also enable the middle value by checking the box to add a middle color. Then click on OK.
Now, you can see the clustered bar chart conditionally formatted in Power BI.
Conclusion
I hope you followed all the steps to create a clustered bar chart in Power BI. In this Power BI tutorial, we explored many ways to format the clustered bar chart, like Sorting the bars, Adding space between two bars, and increasing bar width. Lastly, we saw how to apply conditional formatting in the Power BI clustered bar chart.
Also, you may like:
- Power BI Bar Chart + Conditional Formattings
- Show Zero Values in Power BI Bar Chart
- Power BI Bar Chart Show Value and Percentage
- Power BI Bar Chart Rounded Corners
After working for more than 18 years in Microsoft technologies like SharePoint, Microsoft 365, and Power Platform (Power Apps, Power Automate, and Power BI), I thought will share my SharePoint expertise knowledge with the world. Our audiences are from the United States, Canada, the United Kingdom, Australia, New Zealand, etc. For my expertise knowledge and SharePoint tutorials, Microsoft has been awarded a Microsoft SharePoint MVP (12 times). I have also worked in companies like HP, TCS, KPIT, etc.
