Building charts in Power BI is really easy, but implementing specific features within that chart can be difficult. One of the most common requirements that I’ve had to work on being a Power BI consultant is ordering a stacked bar chart to show a specific ordering of the categories. Although this is not a default function within Power BI, in this blog I will show you how to work around this limitation. This trick works with other chart types, but in this example, we will be using a stacked bar.
In addition, I will share a couple of other custom sorting techniques at the end of the blog post. Let’s get sorting!Â
What's the Issue?
See the stacked bar chart below. The x axis represents the total of the revenue categories for each NCAA Conference, and the y axis is each Conference. The bar charts are colored by type of revenue. By default, Power BI orders the categories within the bars alphabetically. The legend represents this same order (outlined in red).Â
If you click on the ellipses on the top right of this chart element you will see a Sort By option along with the fields that you can sort by. Unfortunately, this only applies to the y axis in this chart type. So select Sort By and choosing Revenue will sort the NCAA Conferences on the left hand side by Revenue. This will not change the sorting of the categories within the bars.Â
Setting up a Sort Table
To change the order of fields within our stacked bar we are going to set up a sort table that will assign a value to each category to be sorted by. First we need to set up a new table in our data model. You can click Enter Data in the Report tab. This will prompt you for your data.Â
Since I want to sort the fields in the stacked bar, I need a column in my new table with those same values in order to create the proper relationship with the original table. I had these field names in an Excel spreadsheet, so I just copy and pasted them into the Enter Data area which is a cool little Power BI shortcut.Â
Once you’ve added in the field names that you want to sort, then create a column that indicates the order that you want your field to appear in. See the column above named Sort.
Bringing in Sort Table to Data Model
Now that we’ve set up the Sort Table, let’s create the proper relationship between our original data set and the new table. Because I used the values that were present in my field Attribute from my base table, this is the field that I will use to create a relationship with my Sort Table. Each value is listed once in my Sort Table and many times in my base table so this is set up as a Many-to-One relationship.Â
Once our table is brought in and relationship has been set up, let’s bring in the Sort column to our original table as a calculated column using the RELATED function.Â
// Sort Column
RELATED('Sort Table'[Sort])
Finalizing our Sort
Now that our sort field has been brought in, the final step is to set the sorting of our Attribute column to be the new Sort field. Go to the Data view and click on the original data set to see all of the field. Next, click on the field that you want to sort, in this case Attribute. When you click on the field in the Fields pane it will be highlighted in the table view.
Lastly, go to the Column Tools ribbon that now is available upon clicking the Attribute field and select Sort by column. The drop down will show you the fields you are able to sort by. Select the Sort Column that we added via our Sort Table.Â
Go back to the Report view in your report and now check the order of the column fields. You will see that they have now taken the order of the Sort Column field.Â
To reiterate, this work around works with other chart types as well, but the stacked bar has been the most common one that I have seen.Â
Additional Sorting Methods
In addition to creating a sorting table, there are a couple of different ways to do custom sorting that I wanted to point out.Â
Calculated Column
If you want to simplify your custom sorting, you can create a calculated column to house your sorting logic. In the example below there are four different categories that we want sorted in a specific order; Rural, Urban, Mix, Youth. You can see how we use a cascading IF statement to accomplish this.Â
//Custom Sort =
IF (
VALUES ( 'Product'[Category] ) = "Rural",
1,
IF (
VALUES ( 'Product'[Category] ) = "Urban",
2,
IF (
VALUES ( 'Product'[Category] ) = "Mix",
3,
IF ( VALUES ( 'Product'[Category] ) = "Youth", 4, 0 )
)
)
)
Once the calculation is complete, you are able to drag this field to the Tooltip section of your visual. Then choose Sort By and select this Custom Sort field.Â
Categorical Sorting
Another example of custom sorting involves the ability to sort items based upon different categories that are not in the actual chart themselves. The example below is a bar chart that shows Segments as the x-axis. In this example we want to see the Segments sorted by Category, alphabetically. By dragging the Category into tooltips and then sorting by First Category, you are able to then sort the Segments into their different Categories. Adding the Category field to Legend allows you to see how they are broken out.Â
Hopefully these additional custom sorting methods were of help in your journey to customize Power BI visuals.Â
