You are currently viewing Power BI Custom Sort order in Bar Chart
Power BI Custom Sort Order Stacked Bar Chart

Power BI Custom Sort order in Bar Chart

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

Power BI Custom Sort order in Bar Chart
Power BI Custom Sort order in 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.

custom sorting Power BI | Power Platform Geeks
custom sorting Power BI

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).

Custom sort orders in Power BI - Table Structure
Custom sort orders in Power BI – Table Structure

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

  • Axis: Status.
  • Legend: Status.
  • Values: Projects Count.
Custom sort orders in stacked bar chart

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

power bi sort by column | Power Platform Geeks

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

Custom sort orders in Power BI
Power BI custom sort order is not available in Stacked Bar Chart

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.
Create a Lookup Table in Power BI
  1. The “Create Table” should be opened now to add your lookup values.
  2. 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.
  3. Finally, set the Table name, and click Load to load the newly created table.
Create a table in power bi using enter data

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“.
Sort by column in power bi

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.
manage relationships in power bi
  1. At the first table, select the original table that holds the column you are need to sort.
  2. At the second table, select the newly created lookup table.
  3. Make sure that the columns have been mapped successfuly, and the “Cardinality” is set to “Many to One”, then click “OK“.
cardinality relationship in power bi

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.

custom sort in stacked bar chart power bi

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

custom sort order in power bi
Power BI custom sort order Ascending in Stacked Bar Chart

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

custom sort order desc in power bi
Power BI custom sort order Descending in Stacked Bar Chart

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
See Also

Mohamed El-Qassas

I'm Microsoft MVP, SharePoint StackExchange Moderator, Microsoft QnA Moderator, C# Corner MVP, Microsoft TechNet Wiki Judge, and Senior Technical Consultant with +12 years of experience in SharePoint, Project Server and Power Platform.