Custom Sorts in Power BI – Step by Step Guide

When working with Power BI, we often face situations where the default sorting (alphabetical or numerical) does not meet business needs. For example, you may want to display product categories in the order High → Medium → Low instead of the standard alphabetical sequence. To achieve this, Power BI allows us to create a custom sort order by building a helper (dummy) table that defines the sequence.

In this article, we’ll walk through how to create a custom sort using a dummy table with an example on product categories.

Why Custom Sort is Needed?

By default, Power BI sorts text fields alphabetically. While this works in many cases, it’s not always practical for reporting. Consider these scenarios:

• Sorting performance ratings as High → Medium → Low instead of alphabetically.
• Sorting months in calendar order (Jan → Feb → … → Dec).
• Sorting custom business hierarchies like Platinum → Gold → Silver → Bronze.

Without custom sorting, your visuals may show misleading or confusing results.

Step 1: Identify the Field for Sorting

In our case, we have a table that displays product categories (High, Medium, Low) along with product names, sales, and product count. By default, Power BI would sort these categories alphabetically (Medium, Low, High as shown in the screenshot), but we want them in a logical order:
High → Medium → Low

Step 2: Create a Dummy Table for Sorting

To apply custom sorting, we’ll create a new dummy table that stores the category along with its sort order.

• Go to the Fields pane in Power BI.
• Right-click on the existing table (Product Categories Table).
• Select Edit Query (as shown in the screenshot on the right).

This opens Power Query, where we can either create or duplicate a table for sorting purposes.

Step 3: Duplicate the Table

• Right-click again on Product Categories Table.
• Select Duplicate Table.
• This creates a copy of the table that we will use as a Sort Order Table.

Step 4: Rename the Duplicated Table

After duplicating the original table, rename it for clarity. For example, rename Product Categories Table (2) to simply Categories_. This ensures you know this table is specifically for sorting purposes and doesn’t affect the original dataset.

Step 5: Keep Only the Required Column

In the duplicated table, select the column you want to use for sorting (in our case, Category).

• Right-click on it and choose Remove Other Columns
• This step will keep only the Category field and remove all other fields.

Step 6: Remove Duplicate Values

Since categories can repeat multiple times (e.g., “High” may appear for several products), we only want unique category names.

• Right-click on the Category column.
• Select Remove Duplicates.
• Now, you’ll have a clean list of unique category values.

Step 7: Create a Custom Sort Order Column

Now, we need to assign a numeric value to each category to control the sort order.

1. Go to the Add Column tab.
2. Select Conditional Column.
3. In the Conditional Column dialog:

• If Category = High, assign value = 1
• If Category = Medium, assign value = 2
• If Category = Low, assign value = 3
• (You can also add a default value if needed.)

This way, each category has a number representing its rank, and Power BI will use this column to sort the categories properly.

Step 8: Finalize the Conditional Column

After creating the conditional column for sort order (High = 1, Medium = 2, Low = 3):

• Click OK.
• Rename the column to something meaningful (e.g., SortOrder).
• Go to the File menu → select Close & Apply to apply the changes and return to Power BI Desktop.

Step 9: Create a Relationship Between Tables

Next, establish a relationship so Power BI knows how to use this sort order.

• Go to the Model View.
• Create a Many-to-One relationship between the Category column of the original Product Categories Table and the Category column in the new Categories (dummy) table.

Step 10: Apply Sort by Column

• Switch to the Data/Table View.
• Select the Category column in your dummy table
• On the ribbon, click Sort by Column and choose SortOrder (the numeric column you created).

This ensures that your categories will always follow the defined custom sequence.

Step 11: Add Category to Your Visual

Drag the Category field into the Rows section of your visual. Power BI will now apply the custom order automatically, using the SortOrder column created. This ensures your categories always appear in the defined sequence rather than the default alphabetical order.

Final Thoughts

Custom sorting in Power BI is a powerful way to make your reports more intuitive and aligned with business logic. By creating a dummy sort table and assigning numeric sort values, you gain full control over how categories, months, or hierarchy levels appear in your visuals.
This approach can be extended to:

• Sorting months in financial calendars.
• Displaying customer tiers in a meaningful sequence.
• Ordering product categories, project phases, or KPIs.

With just a few steps, you can turn confusing alphabetical sorting into clear, business-friendly visuals.