A question often asked is 'how can I do a custom sort in Power BI'. It's a common need but its implementation is not straightforward in Power BI. In this post, I will walk you through it in the easiest possible way.

As an example, I will start with a Sales table that has 3 columns: business regions (EMEA stands for Europe, Middle East, and Africa), month of sales, and sales amount.

This table is the base for a slicer and a bar chart.

Power BI sorts the regions in alphabetical order, while a more meaningful order for the business is Asia first, followed by North America, Latin America, EMEA, Asia Pacific.

Copy the values to sort

The first step in creating a custom sort is to copy the list of regions. For that, we will use a DAX formula that returns the list of Regions, without duplicates.

Click on Modeling, then New Table. This creates a Power BI calculated table based on a DAX formula.

A text field appears. Enter the following DAX formula and click on Commit:

Table = VALUES('Sales'[Regions])

The newly created table appears just below.

Right-click on Regions and choose Copy. This copies the list of Regions.

Create a new Power BI table with the custom sort order

With the list of regions that are now copied, we create a regular (non-calculated) table. In the Home tab, click on Enter Data.

Paste the Regions list in Column 1.

Click on the star at the top-right to insert a new column.

Type your custom order.

Give a name to the table, Regions for example, and click Load.

The copied list of Regions is now no longer necessary, so you can delete the first added table: right-click on it and choose Delete from the model.

Sort Regions based on the newly created column

Select the Regions column. 

Then in 'Sort by Column' choose Regions order.

The last step is to make sure there is a connection between the two tables.

Click on 'Model' on the left side to visualize the data model. Here, verify that there is a connection between the initial table and the table with the order. If there is not a connection, drag the Regions column from the Regions table to the Sales table, this creates a connection.

You are now done with the preparation part! You can update your visualization

Update your Power BI visualizations and obtain your custom sort

Go back to your visualization and replace Regions from the Sales with Regions from the new table.

If the visualization is not correct yet, click on the three dots at the top right and choose Sort by Regions.

Congratulations! Your Power BI now has the columns sorted in your custom order!

Enjoyed the article? Subscribe to the newsletter and download the Power BI of the article for free.