In this post, we will learn how to perform a Power BI custom sort order in Stacked Bar Chart.

Custom Sorting in Power BI

If you try to build a new Stacked Bar Chart in Power BI, you’ll notice that the default sort order is organized in descending order by the value of the Axis field, as shown below.

In our example, we have a table with the following columns that we need to sort in a specific order based on the column “Status” (High – Medium – Low).

In the Power BI Stacked Bar Chart, we have set the fields as below

• Axis: Status.
• Legend: Status.
• Values: Projects Count.

Also, we have tried to use the sort by column functionality in the visual option, as below

But it still doesn’t meet our needs because it sorts by field value rather than by our custom order needs (High – Medium – Low)!!!

Power BI Custom Sort order in Stacked Bar Chart

To overcome the Power BI Stacked Bar Chart’s default sort order behavior, we have to do the following:

Steps

1. Create a Lookup table for the correponding column that you would like to sort by it.
2. In the lookup table, Sort by column ID for Status column.
3. Manage the relationship between the new lookup table and the original table.
4. In Stacked Bar Chart, Set the Axis, Legend, with the new status field in the lookup table.

1) Create a Lookup Table in Power BI

As per your requirements, you have to create a new lookup table that will hold new main columns

• ID (A unique Identifier)
• Attribute (In our example, it will hold the correponding value for each status (High- Medium – Low))

Steps

1. At the “Home” tab, click on “Enter Data” to create a new table.

2. The “Create Table” should be opened now to add your lookup values.
3. Add a column called “ID” to serve as a unique identifier, and another called “Status” (or Attribute, if you want) to carry the corresponding values you’ll need to sort it.
4. Finally, set the Table name, and click Load to load the newly created table.

2) Sort by Column in Power BI

After loading the new table, you can find it in the “Fields” section, and now you have to sort the new lookup column Status by ID as the following:

Steps

1. At the left side, Click on “Data” icon.
2. Make sure that the lookup table is shown as below.
3. Click on Status column, then from the above ribbon, click on “Sort By Column” and select “ID“.

3) Manage Relationships in Power BI

Now, you just need to create a new relationship between the newly created lookup table and the original table as many to one by doing the following:

Steps

1. At the left side, click on “Model” Icon.
2. From the above ribbon, click on “Manage Relationships“.
3. Click on the “New” button to add a new relationship.

4. At the first table, select the original table that holds the column you are need to sort.
5. At the second table, select the newly created lookup table.
6. Make sure that the columns have been mapped successfuly, and the “Cardinality” is set to “Many to One”, then click “OK“.

4) Set Axis and Value Fields in Stacked Bar Chart

Now, you just need to set the Stacked Bar Chart Axis and Legend field to the “Status” column that has been created in the new lookup table as shown below.

5) Sort a Column with a Custom Order in Power BI

Great, the Stacked Bar Chart is now sorted by Status column in our custom order as shown below

Moreover, you can also specify the sort type in descending order as shown below.

You may be also intersted to read How to show Zero values in Power BI Chart?

Conclusion

By default, the Stacked Bar Chart in Power BI is ordered from highest to lowest value, and in this article, we have attempted to provide a simple method for sorting bar chart columns in a custom order as you prefer.

Downlaod

• You can download the PIBX file at Power BI Custom Sort Order Stacked Bar Chart, and don’t forget to follow Power BI Repo to get more PIBX files samples with complex DAX formulas.

See Also

• Subscribe to Power Platform Geeks
• Show items with no data as a Zero in Power BI
• Power BI: RELATED Vs LOOKUPVALUE DAX