Microsoft Excel has a handy feature that allows you to create a custom sort. You won’t need it often, but when you do, it solves a major problem. For example, if you sell t-shirts and you want to sort the data by size values such as Small, Medium, Large, and XL, you can use custom sorting in Excel make things easier. Microsoft Power BI doesn’t have this feature, but you can sort one column on another, which is basically the same and easier to implement.
TO SEE: The Complete Microsoft Power BI Super Bundle (TechRepublic Academy)
In this tutorial, I’ll show you step-by-step how to sort a Power BI column by another column. I use Microsoft Power BI Desktop on a Windows 10 64-bit system, but you can also use the Microsoft Power BI service. Yyou can download the Microsoft Power BI demo file for this lesson.
Jump to:
What does this Power BI tutorial cover?
In this tutorial, we’ll sort the monthly sales by month, which will be sorted alphabetically instead of chronologically: Monday, Tuesday, Wednesday, and so on. 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 number.
You may be wondering why you can’t sort by month numbers when they are available. This is because you can’t sort a visualization by a field that isn’t in the visualization in Power BI.
How to recognize the need to sort by a different column in Power BI
We sort alphabetically and numerically, and usually that’s an adequate approximation. However, sometimes a sort on the actual values does not work. Image A shows a line chart based on twelve records – monthly sales. This file contains only one table, which means that the file depends on the Auto Date table. The example is deliberately simple because we will be working at the table level. No external tables, data and relationships are needed.
Image A
TO SEE: Create a top calculated table in Microsoft Power BI (TechRepublic)
The Month column is in the bucket on the X-axis and Sales is in the bucket on the Y-axis. By default, the visualization sorts by sales value, which messes up the month order along the X-axis. As it is, the visualization is misleading. If you weren’t paying attention, you’d think your sales were going downhill fast.
You can try to correct the monthly sort by using the visualization as follows:
1. Click the More options icon, the three dots. These icons tend to move, so they can be in the top right or bottom right of your visualization.
2. Choose Sort Axis. As you can see in Figure B, Sum of sales is the default setting. Power BI adds “Sum of” because it is a numeric field.
Figure B
3. Choose Month from the submenu to use the X-axis — the months.
At first glance, the graph in Figure C seems more reasonable, with ups and downs, but look closer. Power BI sorted the months alphabetically and in descending order. Again, unless you pay attention, the results are misleading. We need to sort the months chronologically: January, February, March and so on. Those results will be meaningful, but how do we get them?
Figure C
Add a month number field in Power BI
We’ve found that a normal sort doesn’t sort the months in chronological order. The next step is to find a way to enforce 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 is that 12.
If you’re lucky, the month number is already in the table. If that’s the case for you, simply sort the month name field by the month number field and you’re done.
TO SEE: Add a year running total in Microsoft Power BI (TechRepublic)
In most real-life examples, the data is not so perfect. Our model is a good example, because the table does not contain 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 just the name of the month. It’s almost impossible to add a calculated column which returns the month number, but also doesn’t return a circular reference error when you try to sort.
A measure will not work because the timing is not right. For this technique to work, you must add a new column to the month numbers table. Both the month name field and the month number field must share granularity or level.
For this to work, we need to add a new column and fill it with the correct values. Specifically, we will run a query that adds an index column.
Before doing this, the original data, or month names, must be in chronological order. We are 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 what your dataset looks like 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 panel, click the Add Column tab.
3. In the General group, click the Index Column drop-down list and choose From One (Digits E).
Digits 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 starting with 1 that corresponds to the name of the month. Now rename the Monthly Numbers column. Technically this isn’t necessary, but I recommend giving the column a meaningful name so that the data is easier to decipher for all relevant users.
Figure F
TO SEE: 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 are in a situation where your month names are not listed chronologically. In that case, you can copy the table to an Excel sheet and create a custom sort that sorts the month names in chronological order. 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 need to run the index query because the month numbers are in the dataset. For our example, we don’t need to do these steps in Excel because our month names are already in chronological order.
Now it’s time to sort and correct that visualization.
Sort one column after another in Power BI
Adding the month numbers field does nothing to the visualization. Keep in mind that in the visualization we can only sort by two fields: Month and Sales. The new column is not in the visualization and trying to add it will make a mess. We have to find another way.
To push the sort in the visualization, do the following:
1. In the Fields pane, select the field you are sorting, which is Months. You want to see the month names in chronological order.
2. In the Sort contextual group, click the Sort by column drop-down list 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 names of the months are finally in chronological order. The poorly rendered data on the first chart made it seem like the company was having a terrible year, but now it’s clear that business is booming after a few dips. If you sort by the visualization’s, Month, or Sales Amount options, you don’t lose anything because the underlying sorting is based on the Monthly Number values.
If you import an updated table later, don’t forget to add the column or you will lose the sorting function. If an update contains months for the next year, this technique will not work because sorting doesn’t take the year into account. 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 tutorials and resources
Power BI is one of the most widely used and effective business intelligence tools on the market, but like many other business software solutions, the number of features and functions can be overwhelming for the average user. We’ve put together the following top tutorials, training courses, and other resources to help Power BI users get the most out of their business intelligence toolsets:
If you can’t find the right resources in this list or our others Big Data sources, we’d love to hear what questions you have and what tutorial topics we should cover next. Use the contact form below to contact us if you are interested.
Read next: Best business intelligence tools (TechRepublic)
