How to Sort Clustered Bar Chart in Power BI

A cluttered bar chart trying to tell a story alphabetically is like a book with its pages out of order. While Power BI is a fantastic tool for visualizing data, its default tendency to sort the categories on your clustered bar charts alphabetically can often hide the key insights you’re trying to find. This guide walks you through the straightforward methods to take control of your chart’s sorting, so you can present your data clearly and logically.

Understanding Power BI's Default Sorting Behavior

Before fixing the issue, it helps to know why it happens. When you create a clustered bar chart in Power BI with a category on one axis and a legend to create the clusters, Power BI has to make two sorting decisions:

• The Axis Sorting: This is for the main categories. For example, if you're charting sales by region, your regions are on the axis. Power BI's default behavior is to sort these text-based categories alphabetically (e.g., Central, East, South, West).
• The Legend Sorting: This is for the different colored bars within each cluster - for example, product types. These are also sorted alphabetically by default.

The problem is, an alphabetical sort is rarely the most insightful. You usually want to sort your regions by their total sales performance, from highest to lowest, to quickly see who your top performers are. Trying to do this with the basic sort options can be confusing because Power BI isn't sure if you want to sort by the total sales of the region or just the sales of a single product within that region. Let's fix that.

Method 1: The Sorting Measure and Tooltip Trick

This is the cleanest and most effective way to sort your clustered bar chart by its total value. We’ll use a simple DAX measure and a neat feature of the "Tooltips" field well to unlock the sorting option you need.

Let's use a common scenario: you have a clustered column chart showing Sales Revenue by Region (on the Y-Axis) and Product Category (in the Legend). Your goal is to sort the regions not alphabetically, but by the combined sales of all product categories.

Step 1: Create a "Grand Total" Measure in DAX

Even if you're already using a sales column in your chart, creating an explicit DAX measure for sorting is good practice and makes your model easier to understand. This measure will simply sum up the total value we want to sort by.

1. Navigate to the Modeling tab in the Power BI ribbon or right-click your table in the 'Data' pane and select New measure.
2. In the formula bar that appears, type the following DAX formula. Remember to replace 'SalesTable'[Revenue] with your own table and column name.

Sort by Total Regional Sales = SUM('SalesTable'[Revenue])

Press Enter to save the measure. It will now appear in your 'Data' pane with a small calculator icon next to it.

Step 2: Add the New Measure to the Tooltips Field

This is the magic step. The "Tooltips" data field in the Visualizations pane isn't just for showing extra information when you hover over a data point, it also makes fields available to the visual for other functions, including sorting.

1. Make sure your clustered bar chart is selected.
2. Go to the Visualizations pane.
3. Find your new measure, Sort by Total Regional Sales, in the 'Data' pane.
4. Click and drag this measure into the Tooltips bucket, right below your other data fields.

Visually, nothing will change on your chart yet. However, you've just told Power BI that this new total value is now associated with your chart.

Step 3: Sort the Visual Using Your New Measure

With the new measure loaded into the Tooltips, you can now use it to control the sort order.

1. On your chart visual, click the ellipsis icon (...) in the top-right corner to open the 'More options' menu.
2. Hover over Sort axis. A new sub-menu will appear.
3. You should now see your measure, Sort by Total Regional Sales, listed as a sorting option!
4. Click on it to apply the sort.
5. You may need to click it again to change the direction from ascending to descending to get the highest value on top. Go back to the menu (... > Sort axis) and pick your preferred direction.

That's it! Your clustered bar chart’s axis will now be sorted from highest total sales to lowest, giving you a much clearer picture of performance across regions.

Method 2: Create a Custom Sort Order with "Sort By Column"

Sometimes, your desired sort order has nothing to do with values. You might need to sort data in a specific, non-alphabetic, non-numeric logical sequence, like months of the year, days of the week, or custom levels of importance (e.g., "High," "Medium," "Low").

Let's say your chart shows data by Month Name. Alphabetically, this would appear as April, August, December, etc., which is not useful. Here’s how to fix it using the "Sort by Column" feature.

Step 1: Create a Helper Column with a Sort Index

The key to this method is having a second column that provides a numerical order for your text-based column. You can create this in your original data source (like Excel), in Power Query during data import, or directly in Power BI's Data view.

Let's assume you have a 'Date' table in your Power BI model that includes a column for MonthName. You need to add a MonthNumber column.

1. Go to the Data View (the table icon on the left-hand navigation bar in Power BI).
2. Select the table containing your month name column (e.g., 'Dates').
3. In the Table tools or Column tools ribbon, select New column.
4. Enter a DAX formula to create the month number. If you have a date column, it's easy:

Month Number = MONTH('Dates'[Date])

This will create a new column pairing "January" with 1, "February" with 2, and so on. If you don't have a date column, you can create a conditional column using logic like an IF or SWITCH function.

Step 2: Apply the "Sort By Column" Feature

Now you need to tell Power BI to use your new numerical column as the sorting guide for your text column.

1. While still in the Data View, click to select the entire column you want to define the sort order for (in our case, the MonthName column).
2. Go to the Column tools ribbon at the top of the screen.
3. Click the Sort by column button.
4. From the dropdown menu that appears, select the helper column you just created (the Month Number column).

Power BI will take a moment to process this dependency. It now understands that any time it displays the MonthName column in a visual, it should order it based on the Month Number column.

Step 3: Check Your Chart

Return to the Report View. Your chart may have updated automatically. If not:

1. Select your clustered bar chart.
2. Click the ellipsis (...) menu > Sort axis.
3. Select your axis field (e.g., MonthName).
4. Then choose Sort ascending.

Your chart should now correctly display months in chronological order: January, February, March, and so on.

Common Sorting Pitfalls and Quick Fixes

• My "Sort Measure" Isn’t in the Options: The #1 reason this happens is forgetting to drag your sorting measure into the Tooltips field well for that specific visual. Double-check that it's there.
• "Sort By Column" is Greyed Out: This usually means there's a problem with the relationship between your two columns. The "Sort by column" feature requires a strict one-to-one relationship - each unique value in your primary column (e.g., "January") must correspond to only one value in your sort column (e.g., 1). Check for duplicate or inconsistent entries in your data.
• The Sort is Backwards: If your chart is sorted with the lowest values on top, you just need to change the sort direction. Go back to the ellipsis menu (... > Sort axis) and switch between Sort ascending and Sort descending.

Final Thoughts

Don't let Power BI's default alphabetical sorting compromise the clarity of your reports. By using a quick DAX measure with the Tooltips trick for value-based sorting, or setting up a helper column for custom logic, you can ensure your clustered bar charts tell their story in the right order, making your data much easier to interpret.

Mastering these details is what separates a decent report from a great one, but we know first-hand that tweaking visualizations and wrestling with reporting tools can become a huge time sink. We designed Graphed to remove this friction by allowing you to create entire dashboards in seconds. Instead of navigating menus, you simply describe what you need, like "show me our total sales by region and product line, with regions sorted from highest to lowest sales," and it builds the visual for you, getting you from data to decision almost instantly.