How to sort one column by another column in Microsoft Power BI


Power BI logo on a laptop.
Photo: monticello / Adobe Stock

Microsoft Excel has a neat feature that allows you to create a custom sort. You won’t need it often, but when you need it, it solves a big problem. For example, if you sell T-shirts and want to sort data by size values ​​like Small, Medium, Large, and XL, you can use custom sorting in Excel to make things easier. Microsoft Power BI does not have this feature, but you can sort column by column, which is effectively the same thing and easier to implement.

See: Complete Microsoft Power BI Super Pack (TechRepublic Academy)

In this tutorial, I will show you step by step how to sort a Power BI column by another column. I’m using Microsoft Power BI Desktop on Windows 10 64-bit, but you can also use Microsoft Power BI service. sYou can download the Microsoft Power BI demo file for this tutorial.

jump to:

What does this Power BI tutorial cover?

In this tutorial, we’ll sort monthly sales by month, which are sorted alphabetically rather than chronologically: Monday, Tuesday, Wednesday, etc. We will also add the month numbers and sort the name field by the number field. It’s a simple technique that you can apply to similar situations, such as sorting T-shirt orders by size values ​​or sorting days by their numbers.

You may be wondering why you can’t sort by month numbers if they are available. This is because you cannot sort a visualization by a field that is not in the Power BI visualization.

How to recognize the need to sort by another column in Power BI

We sort alphabetically and numerically, and this is usually a convenient technique. However, sometimes sorting by actual values ​​does not work. Figure A Displays a line chart based on twelve records – monthly sales. This file contains only one table, which means the file is based on the Auto Date table. The example is intentionally simple because we’ll be working at the table level. There is no need for extraneous tables, data and relationships.

Figure A

By default, visualization sorts monthly sales values.
By default, visualization sorts monthly sales values.

See: How to create a calculated table of top values ​​in Microsoft Power BI (TechRepublic)

The month column is in the x-axis container and sales are in the y-axis container. By default, the visualization sorts by sales value, cluttering up the month’s demand along the x-axis. As it is, the perception is misleading. If you weren’t paying attention, you thought your sales were declining quickly.

You might try to fix the month sort by using a visualization like this:

1. Click the more options icon, which is the three dots. These icons tend to move around, so they can be at the top or bottom right of your visualization.

2. Choose “Sort Axis”. as you see in Figure B, Total sales is the default. Power BI adds “sum” because it is a numeric field.

Figure B

Choose the month field to sort.
Choose the month field to sort.

3. Choose the month from the sub-menu to switch to the X-Months axis.

At first glance, the graph appears in the format Figure C It makes more sense, and shows ups and downs, but look closely. Power BI sorts the months alphabetically and in descending order. Again, unless you are paying attention, the results are misleading. We need to sort the months in chronological order: January, February, March, etc. These results will be meaningful, but how do we get there?

Figure C

Sorting by month will not work alphabetically.
Sorting by month will not work alphabetically.

How to add a month number field in Power BI

We have demonstrated that regular sorting will not sort the months in chronological order. The next step is to find a way to force the monthly sort, and it comes in the form of a number: month number where January 1, February 2, March is 3 and even December is 12.

If you are lucky, the table already contains the month number. If that’s the case for you, you’ll simply sort the month name field by the month number field, and you’re done.

See: How to add a run total for the year to date in Microsoft Power BI (TechRepublic)

In most real-world examples, the data is not perfect. Our model is a good example, because the table does not have a month number in the Sales by Month table. To make matters worse, the month value we have is text, not a date formatted to display only the month name. It is almost impossible to add a computed column that returns the month number but also does not return a circular reference error when trying to sort.

The gauge will not work because the timing is off. For this technique to work, a new column must be added to the table for the numbers of the months. The month name field and the month number field must have the same precision or level.

To make this work, we must add a new column and fill it with the correct values. Specifically, we are going to run a query that will add an index column.

Before doing this, the original data or the names of the months should be in chronological order. We’re in good shape because our records are arranged chronologically by month name. This won’t always be the case, so it’s important to pay close attention to how your data set looks before you start.

To add an index column, run a simple query as follows:

1. Right-click on Sales by Month in the Fields pane and choose Edit Query from the resulting submenu (figure d).

figure d

Run a query.
Run a query.

2. In the resulting query window, click on the Add Column tab.

3. In the General group, click on the Index Column drop-down list and choose from one (Figure E).

Figure E

Create an index field starting with the number 1.
Create an index field starting with the number 1.

4. Close and save the query when prompted.

as you see in shape and, the table now contains a column of consecutive numbers starting with the number 1 that corresponds to the name of the month. At this point, rename the column “Monthly Numbers”. Technically, this is not necessary, but I recommend you give the column a meaningful name to make it easier for all relevant users to decode the data.

shape and

Add a table of consecutive values ​​that chronologically match the corresponding months.
Add a table of consecutive values ​​that chronologically match the corresponding months.

See: How to add action buttons to a report in Microsoft Power BI (TechRepublic)

It’s worth noting again that the names of the months must be in chronological order for this to work. Suppose you find yourself in a situation where the names of your months are not listed in chronological order. In this case, you can copy the table to an Excel sheet and create a custom sort that sorts the month names in chronological order. While you are in Excel, go ahead and enter the corresponding month numbers. Then import it back into Power BI and you’re good to go. You don’t even have to run the index query because the month numbers are in the dataset. For example, we won’t need to take these steps in Excel because our month names are already arranged chronologically.

Now is the time to sort out and fix this perception.

How to sort a column by another in Power BI

Adding the month numbers field does nothing in the visualization. Remember that we can sort by only two fields in the visualization: month and sales. The new column is not in the visualization, and if you try to add it, it will make a mess. We must find another way.

To drive sorting into visualization, do the following:

1. In the Fields pane, select the field you are sorting, which is the most popular. You want to see the names of the months in chronological order.

2. In the contextual sort group, click the Sort by column dropdown menu and choose Monthly Number (shape g).

shape g

Select the monthly column.
Select the monthly column.

The most popular results are shown in descending chronological order. To fix this, click More options (Figure B) and choose ascending order. shape h Shows results.

shape h

Sort the month field by the new field added earlier: Monthly Number.
Sort the month field by the new field added earlier: Monthly Number.

Finally, the names of the months are arranged in chronological order. The data not represented in the first graph showed that the company appears to have had a terrible year, but it is now clear that after some dips, the business is booming. If you sort by visuals options, month or total sales, you won’t lose anything because the primary sorting is by monthly number values.

If you import an updated table later, remember to add the column or the sort function will be lost. If an update includes months for the next year, this technique won’t work because it doesn’t take the year when sorting. In this case, you need the month number field which contains the month and year number. With that, you now have the steps and resources to sort a Power BI column by another column.

Power BI tutorials and resources

Power BI is one of the most widely used and powerful business intelligence tools on the market, but like many other enterprise software solutions, the number of features and functionality can be overwhelming for the average user. We’ve collected the following best tutorials, courses, and other resources to help Power BI users get the most out of their Business Intelligence toolkits:

If you haven’t found the right resources in this list or among other big data resources, we’d love to hear what questions you have and what educational topics we should cover next. Use the contact form below to get in touch if you are interested.

Read next: Best Business Intelligence Tools (TechRepublic)