Power BI: Custom sort a column
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.
![Initial table Power BI custom sort](https://finance-bi.com/blog/wp-content/uploads/2019/10/initial-table.png)
This table is the base for a slicer and a bar chart.
![Initial slicer and chart](https://finance-bi.com/blog/wp-content/uploads/2019/10/slicer-and-chart.png)
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.
![Create a new calculated table](https://finance-bi.com/blog/wp-content/uploads/2019/10/Create-new-table.png)
A text field appears. Enter the following DAX formula and click on Commit:
Table = VALUES('Sales'[Regions])
![DAX formula for creating a new table in Power BI](https://finance-bi.com/blog/wp-content/uploads/2019/10/Formula-and-Commit.png)
The newly created table appears just below.
![New calculated table appears](https://finance-bi.com/blog/wp-content/uploads/2019/10/New-table-1.png)
Right-click on Regions and choose Copy. This copies the list of Regions.
![Copy values](https://finance-bi.com/blog/wp-content/uploads/2019/10/Copy-Regions.png)
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.
![Enter Data in Power BI](https://finance-bi.com/blog/wp-content/uploads/2019/10/Enter-Data.png)
Paste the Regions list in Column 1.
![Paste the values](https://finance-bi.com/blog/wp-content/uploads/2019/10/Paste-in-Column-1.png)
Click on the star at the top-right to insert a new column.
![Add a column](https://finance-bi.com/blog/wp-content/uploads/2019/10/Add-a-column.png)
Type your custom order.
![Enter the custom order](https://finance-bi.com/blog/wp-content/uploads/2019/10/Enter-Regions-Order.png)
Give a name to the table, Regions for example, and click Load.
![Load the table](https://finance-bi.com/blog/wp-content/uploads/2019/10/Give-Name.png)
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.
![Delete calculated table](https://finance-bi.com/blog/wp-content/uploads/2019/10/Delete-calculated-table.png)
Sort Regions based on the newly created column
Select the Regions column.
![Select the Regions column](https://finance-bi.com/blog/wp-content/uploads/2019/10/Click-on-Regions-column.png)
Then in 'Sort by Column' choose Regions order.
![Sort by Column](https://finance-bi.com/blog/wp-content/uploads/2019/10/Sort-by-regions-order.png)
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.
![Create relationships in the Power BI model](https://finance-bi.com/blog/wp-content/uploads/2019/10/Model.png)
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.
![Update your Power BI visualization](https://finance-bi.com/blog/wp-content/uploads/2019/10/Update-visualization-1.png)
If the visualization is not correct yet, click on the three dots at the top right and choose Sort by Regions.
![Sort visualization by Regions](https://finance-bi.com/blog/wp-content/uploads/2019/10/Three-dot.png)
Congratulations! Your Power BI now has the columns sorted in your custom order!
![Final result - custom sort in Power BI](https://finance-bi.com/blog/wp-content/uploads/2019/10/Final-result.png)
Enjoyed the article? Subscribe to the newsletter and download the Power BI of the article for free.