Graphed LogoGraphed
Login

How to Custom Sort in Power BI

Cody Schneider•

When you create a visual in Power BI, it often defaults to sorting your categories alphabetically. While this works for some charts, it can quickly make your reports confusing when dealing with data that has a natural, non-alphabetical order, like months of the year or survey responses. This guide will walk you through exactly how to set up custom sorting in Power BI to make your reports clear, intuitive, and easy to understand.

Why Default Sorting Can Be a Problem

Imagine showing a sales trend chart where the months are ordered alphabetically: April, August, December, February. This arrangement makes it impossible to see seasonal patterns or monthly growth because the data isn't in chronological order. The narrative is lost, and the viewer has to work hard to piece the story together.

This problem appears in many common business scenarios:

  • Dates: Months of the year (January, February, March) or days of the week (Sunday, Monday, Tuesday) need a specific chronological order.

  • Ordinal Categories: T-shirt sizes (Small, Medium, Large, X-Large) or project phases (Discovery, Design, Development, Testing) have a logical sequence.

  • Survey Scales: Customer feedback like "Very Dissatisfied," "Dissatisfied," "Neutral," "Satisfied," and "Very Satisfied" is meaningless when sorted alphabetically.

Custom sorting solves this by letting you define the exact order for your data, ensuring your visuals tell the right story at a glance.

The Key to Custom Sorts: The "Sort by Column" Feature

The solution is a straightforward but powerful feature in Power BI called a "helper column." The concept is simple: you create a secondary column that contains a numerical value representing the order you want. Then, you tell Power BI to use this helper column to sort your text-based category column.

For example, to sort month names, you would use a "Month Number" column (1 for January, 2 for February, etc.) as your helper. Power BI will then order the chart using the Month Name column but arrange it based on the values in the Month Number column.

Step-by-Step Guide: Custom Sorting Months of the Year

Let's walk through the most common use case: sorting months chronologically. This example requires a date table or, at minimum, a table that contains your dates. If you don't have one, Power BI can usually auto-generate one for you from your date fields.

Step 1: Make Sure You Have the Necessary Columns

For this to work, you need two columns in your data model:

  • The column you want to display (e.g., "Month Name").

  • A column containing its sort order (e.g., "Month Number").

If you have a date table with a date column (let's call it 'Calendar'[Date]), you can easily create these using DAX (Data Analysis Expressions). Go to the Data View in Power BI, select your date table, and create a new column.

Create the "Month Name" column with this DAX formula:

Then, create the "Month Number" helper column that will define the sort order:

Now your table has both the month name (e.g., “January”) and its corresponding number (1), which is what we need for the next step.

Step 2: Apply the "Sort by Column" Rule

With your columns ready, the next step is to tell Power BI about their relationship.

  1. While still in the Data View, click on the Month Name column to select it.

  2. In the ribbon menu at the top, go to the Column tools tab.

  3. Click on the Sort by Column button.

  4. From the dropdown list, select your helper column ("Month Number").

Power BI will process this change silently. When it's done, the "Month Name" column will be sorted according to the "Month Number" values.

Step 3: Test Your Custom Sort in a Visual

Go back to the Report View to verify it works:

  • Drag the "Month Name" column to the Axis of a chart (e.g., bar chart).

  • Drag a measure like "Sales Amount" to Values.

  • Click the ellipsis (...) menu at the top-right of the visual.

  • Choose Sort axis, then select Sort by -> Month Name, and ensure it is sorted in Ascending order.

Your chart should now display months in the correct chronological order: January, February, March, etc., instead of alphabetically.

More Examples of Custom Sorting

This technique is versatile and applicable to any data that has a logical or custom order.

Example 1: T-Shirt Sizes (Creating a Helper Column)

Suppose you're analyzing apparel sales and have sizes like 'S', 'M', 'L', and 'XL'. Alphabetically, 'L' comes before 'M'. Let's fix that.

Option A: Creating a Conditional Column in Power Query

If you don’t have a sort order column:

  1. Open Power Query Editor (Transform Data).

  2. Select your sales data query.

  3. Go to the Add Column tab, click Conditional Column.

  4. Set rules:

  • If Size equals 'S', then 1

  • If Size equals 'M', then 2

  • If Size equals 'L', then 3

  • If Size equals 'XL', then 4

  1. Name the new column "Size Order" and click OK.

  2. Close & Apply.

Use "Size Order" in the Sort by Column menu to order the sizes logically.

Option B: Using a Separate Dimension Table

  1. In Power BI Desktop, go to Home -> Enter Data.

  2. Create a table:

Size

SortOrder

S

1

M

2

L

3

XL

4

  1. Load this as "Size Dimension".

  2. Create a relationship between your sales table’s Size column and the Size column in the dimension table.

  3. Use the "Size" from the dimension table in your visuals.

  4. Apply Sort by Column on "Size" using "SortOrder".

Example 2: User Satisfaction Survey Scale

Suppose your survey responses are text like "Very Satisfied," "Satisfied," "Neutral," "Dissatisfied." To present these in a meaningful order:

  1. Create a numeric helper column:

Satisfaction

Score

Very Satisfied

5

Satisfied

4

Neutral

3

Dissatisfied

2

Very Dissatisfied

1

  1. Select "Satisfaction" column.

  2. Click Sort by Column -> "Score".

Now, you can sort charts from highest to lowest satisfaction.

Troubleshooting Common Issues

Error Message About Unique Values

The "Sort by Column" feature needs a one-to-one correspondence between the display column and the sort column, i.e., each value must be unique in the sort column. If not, Power BI will give an error.

"Sort by Column" Button is Grayed Out

Make sure you've selected exactly one column in Data View or Model View. The button only works with a single column selected.

Visual Isn't Updating!

After setting the sort order, refresh the visual:

  • Check the Sort by settings in the visual.

  • Sometimes the visual might be sorting by measure or another field, overriding your setting.

Final Thoughts

Setting up custom sorting is a fundamental skill that elevates your Power BI reports from confusing to crystal clear. By leveraging the "Sort by Column" feature with a helper column, you can ensure your data is always presented in a logical, intuitive order that tells the story you intend.

While getting these details right in Power BI is rewarding, we know that connecting data sources and building reports can be time-consuming. That’s why we created Graphed. You can connect your data sources in just a few clicks and build entire real-time dashboards simply by describing what you need in plain English. We handle the complex data prep and visualization automatically, so you can focus on insights instead of configuration.