Microsoft Excel has a neat feature that lets you build a custom sort. You won’t need it often, but when you do, it solves a big problem. For instance, if you’re selling t-shirts and you want to sort the data by size values like Small, Medium, Large and XL, you can use custom sort in Excel to make things easier. Microsoft Power BI doesn’t have this feature, but you can sort one column by another, which is effectively the same thing and easier to implement.
SEE: The Complete Microsoft Power BI Super Bundle (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 a Windows 10 64-bit system, but you can also use Microsoft Power BI service. You can download the Microsoft Power BI demo file for this tutorial.
Jump to:
In this tutorial, we’ll sort monthly sales by the month, which sort alphabetically instead of chronologically: Monday, Tuesday, Wednesday and so on. We’ll 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 might be wondering why you can’t sort by the month numbers if they’re available. This is because you can’t sort a visualization by a field that isn’t in the visualization in Power BI.
We sort alphabetically and numerically, and usually, that’s an adequate approach. However, sometimes a sort by the actual values doesn’t work. Figure A shows a line chart based on twelve records — monthly sales. This file contains only one table, which means the file is relying on the Auto Date table. The example is simple on purpose because we’ll be working at the table level. There’s no need for extraneous tables, data and relationships.
Figure A
SEE: How to create a calculated table of top values in Microsoft Power BI (TechRepublic)
The Month column is in the X-axis bucket and Sales are in the Y-axis bucket. By default, the visualization sorts by the sales value, which makes a mess of the month order along the X-axis. As is, the visualization is misleading. If you weren’t paying attention, you’d think your sales were going downhill fast.
You might try to fix the month sort by resorting on the visualization as follows:
1. Click the More Options icon, which is the three dots. These icons tend to move around, so they could be at the top or bottom right of your visualization.
2. Choose Sort Axis. As you can see in Figure B, Sum of Sales is the default. Power BI adds “Sum of” because it’s a numeric field.
Figure B
3. Choose Month from the submenu to resort the X-axis — the months.
At first glance, the chart shown in Figure C appears more reasonable, showing ups and downs, but look closer. Power BI sorted the months alphabetically and in descending order. Again, unless you’re paying attention, the results are misleading. We need to sort months chronologically: January, February, March and so on. Those results will be meaningful, but how do we get to them?
Figure C
We’ve established that a regular sort won’t sort the months in chronological order. The next step is to find a way to force a monthly sort, and it comes in the form of a number: The month number where January is 1, February is 2, March is 3 and through December which is 12.
If you’re 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 year-to-date running total in Microsoft Power BI (TechRepublic)
In most real-world examples, the data isn’t that perfect. Our model is a good example, because the table doesn’t contain a month number in the Sales By Month table. To make matters worse, the month value we do have is text, not a date formatted to display only the month name. It is nearly impossible to add a calculated column that returns the month number but also doesn’t return a circular reference error when you try to sort.
A measure won’t work because the timing is off. For this technique to work, you must add a new column to the table for month numbers. Both the month name field and the month number field must share granularity, or level.
To make this work, we must add a new column and populate it with the right values. Specifically, we’ll run a query that will add an index column.
Before doing so, the original data, or the month names, must be in chronological order. We’re in good shape because our records are in chronological order by month name. That 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 Sales By Month in the Fields pane and choose Edit Query from the resulting submenu (Figure D).
Figure D
2. In the resulting query window, click the Add Column tab.
3. In the General group, click the Index Column dropdown and choose From One (Figure E).
Figure E
4. Close the query and save when prompted.
As you can see in Figure F, the table now has a column of consecutive numbers beginning with 1 that corresponds to the month name. At this point, rename the column Monthly Numbers. Technically, this isn’t necessary, but I recommend you give the column a meaningful name to make the data easier for all relevant users to decipher.
Figure F
SEE: How to add action buttons to a report in Microsoft Power BI (TechRepublic)
It’s worth noting again that the month names must be in chronological order for this to work. Suppose you find yourself in a situation where your month names are not chronologically listed. In that case, you can copy the table into an Excel sheet and create a custom sort that sorts the month names in chronological order. While in Excel, go ahead and enter the corresponding month numbers. Then import it back into Power BI and you’re ready to go. You won’t even have to run the index query because the month numbers are in the data set. For our example, we won’t need to take these steps in Excel because our month names are already in chronological order.
Now it’s time to sort and fix that visualization.
Adding the field of month numbers doesn’t do anything to the visualization. Remember, we can sort by only two fields in the visualization: Month and Sales. The new column isn’t in the visualization, and if you try to add it, you will make a mess. We must find another way.
To push the sort into the visualization, do the following:
1. In the Fields pane, select the field you’re sorting, which is Months. You want to see the month names in chronological order.
2. In the contextual Sort group, click the Sort By Column dropdown and choose Monthly Number (Figure G).
Figure G
The results show the months in chronological, descending order. To fix this, click More Options (Figure B) and choose Ascending Order. Figure H shows the results.
Figure H
The month names are finally in chronological order. The first chart’s ill-represented data made it look like the business had a horrible year, but now it’s clear that after a couple of dips, business is booming. If you sort by the visualization’s options, Month or Sum Of Sales, you won’t lose anything because the underlying sort is by the Monthly Number values.
If you import an updated table later, remember to add the column or lose the sort functionality. If an update includes months for the next year, this technique won’t work because it doesn’t consider the year when sorting. In this case, you need a month number field that contains the month number and year. With that, you now have the necessary steps and resources to sort a Power BI column by another column.
Power BI is one of the most widely-used and effective business intelligence tools on the market, but like many other enterprise software solutions, the number of features and functions can be overwhelming to the typical user. We’ve pulled together the following top tutorials, training courses and other resources to help Power BI users get the most out of their business intelligence tool sets:
If you’re not finding the right resources in this list or among our other Big Data resources, we’d love to hear what questions you have and what tutorial topics we should cover next. Use the contact form below to reach out if interested.
Read next: Best business intelligence tools (TechRepublic)
