How to Reorder Legend in Power BI
When you create a chart in Power BI, the legend often sorts itself alphabetically, which can turn a perfectly clear visual into a confusing mess. This article will show you exactly how to take control of your legend and reorder it to tell a logical, compelling story with your data.
Why Power BI Legends Sort Alphabetically By Default
Power BI defaults to sorting text-based categories alphabetically because, without any other instructions, it's the most logical default. If you have product categories like "Accessories," "Bikes," and "Clothing," an A-Z sort order works just fine. But the trouble starts when your categories have an inherent, non-alphabetical sequence.
Consider these common scenarios:
Time Periods: Displaying months as "April," "August," "December," "February..." is completely unhelpful. You need them sorted chronologically: "January," "February," "March..."
Performance Tiers: If you use categories like "Gold," "Silver," "Platinum," and "Bronze," alphabetical sorting ("Bronze," "Gold," "Platinum," "Silver") is not as intuitive as ranking them by value ("Platinum," "Gold," "Silver," "Bronze").
Survey Responses: Analyzing feedback like "Excellent," "Good," "Fair," and "Poor" loses its impact when sorted alphabetically ("Excellent," "Fair," "Good," "Poor"). The visual story should flow from best to worst, or vice versa.
Misordered legends force your audience to spend time decoding the chart instead of understanding the insight. The solution is to tell Power BI how to sort these categories by giving it a clear order to follow. Let’s walk through the most effective methods to do this.
The Best Method: Use 'Sort by Column' in Your Data Model
The gold standard for reordering legends in Power BI is the 'Sort by Column' feature. This technique involves creating a "helper" column in your data table that contains a numerical sort order. You then tell Power BI to sort your category column based on the order defined in your helper column. It’s a permanent fix within your data model, which means any visual you build will inherit the correct sorting automatically.
Let's use the most common example: sorting months of the year chronologically.
Step 1: Create a Numerical Sorting Column
First, you need a column that represents the correct order. For months, this would be a column with numbers 1 through 12, where 1 corresponds to January, 2 to February, and so on. If you're using a dedicated Date Table in your model (which is a best practice), you likely already have a 'Month Number' column.
If you don't, you can easily create one using Data Analysis Expressions (DAX). Navigate to the Data view in Power BI, select the table containing your date or month column, and click 'New Column' from the ribbon.
Assuming you have a date column named 'Date', you could use this simple DAX formula:
This formula creates a text column with the month number. To ensure it sorts correctly as a number, not text, select the new 'Month Number' column, go to the 'Column tools' tab, and change the 'Data type' to 'Whole Number'.
Your table should now have both the month name (e.g., "January") and a corresponding month number (1).
Step 2: Apply the 'Sort by Column' Feature
Now it's time to connect the two columns. Still in the Data view:
Click to select the primary column you want to sort – the one used in your legend. In our case, this is the 'Month Name' column. You must select this column first, otherwise the option will be greyed out.
Go to the 'Column tools' tab.
Click the 'Sort by column' option.
A dropdown menu will appear. From this menu, select the helper column you created: 'Month Number'.
Power BI will process the relationship in the background. You won't see anything visibly change in the Data table, but your data model now understands that whenever it sees 'Month Name', it should sort it based on the 'Month Number' column.
Step 3: Verify the Legend in Your Visual
Return to the Report view and look at your chart. The legend, and any other visual element that uses the 'Month Name' column, should now be correctly sorted in chronological order: January, February, March, April, and so on. Your chart is fixed! If you don't see the change immediately, click the three dots (...) at the top of your visual, go to 'Sort axis', and make sure it's sorting by 'Month Name' in ascending order.
Alternative Method: Create a Manual Sort Using a Related Table
Sometimes you need to sort categories that aren't dates and don't have a natural numerical counterpart, like "High", "Medium", "Low". While you could still create a calculated column with DAX logic (e.g., using SWITCH), another clean approach is to create a separate sorting table. This is especially useful if you want to keep your sorting logic separate from your main data table.
Let's reorder 'Customer Segments': Platinum, Gold, Silver, Bronze.
Step 1: Create a New Table with Your Sort Order
From the 'Home' tab in the ribbon, click 'Enter data'.
A dialog box will appear allowing you to create a small table.
Create two columns. Name the first one 'Segment' and the second one 'Sort Order'.
Enter your data in the desired logical order:
Row 1: "Platinum", 1
Row 2: "Gold", 2
Row 3: "Silver", 3
Row 4: "Bronze", 4
Give your new table a name, such as "Segment Sorting", and click 'Load'.
Step 2: Create a Relationship Between the Tables
Navigate to the Model view in Power BI. You'll see your existing data tables and the new "Segment Sorting" table.
Find the 'Segment' column in your new table and the corresponding segment column in your primary data table.
Click and drag the 'Segment' column from the "Segment Sorting" table onto the segment column in your main data table.
Power BI will create a one-to-many relationship, linking the two.
Step 3: Update and Sort Your Visual
Go back to the Data view, select your new "Segment Sorting" table, and use the 'Sort by column' feature to sort the 'Segment' column by the 'Sort Order' column (similar to the first method).
Return to your visual in the Report view.
Remove the original segment field from your visual's 'Legend' field well.
Drag in the 'Segment' field from your new "Segment Sorting" table.
Your chart legend will now adhere to the 1, 2, 3, 4 sorting you defined, displaying "Platinum," "Gold," "Silver," and then "Bronze."
Troubleshooting Common Sorting Problems
"The 'Sort by Column' Option is Greyed Out."
This is a common issue. It usually happens if you haven't selected a column first. Remember, you must first click on the column you want to sort (the text column like 'Month Name'), and then the 'Sort by Column' button will become active, allowing you to choose your numerical sorting column ('Month Number').
"My sort order has an error: to sort by another column, the column... must have unique values."
This message means there isn't a one-to-one relationship between your sort column and your category column. For example, trying to sort 'Month Name' by 'Year' would fail because the category "January" exists in multiple years (January 2022, January 2023, etc.). The value in your text column must uniquely correspond to a single value in your numerical sort column. "January" must always correspond to "1", and "February" must always correspond to "2".
"Can't I just drag and drop the legend order?"
Unlike Excel or Google Sheets, you cannot manually drag and drop items in a Power BI legend to reorder them. Power BI is a data modeling tool, meaning that visual display properties like sort order are rooted in the structure and definitions within your data model. By defining the sort order at the model level, you ensure consistency across your entire report, which is far more efficient and reliable than manually tweaking every single chart.
Final Thoughts
Fixing the legend order in Power BI might seem tricky at first, but it comes down to one core concept: you need a helper column to define your custom sort sequence. By linking your text-based category column to a numerical order column with the 'Sort by Column' feature, you gain full control over how your data is presented and ensure your visualizations are clear, intuitive, and effective.
While mastering tools like Power BI is incredibly powerful, we know that getting bogged down in fixes like reordering legends can pull you away from what's important: getting clear answers from your data. That's why we created Graphed. Instead of fighting with data models and sort settings, you can simply ask for what you want in plain English. Queries like "Show me sales by month this year" automatically generate interactive dashboards where months are sorted chronologically, with no manual intervention needed, allowing you to focus on strategy instead of setup.