Are you looking for a custom sort in the Power BI Bar Chart? If your requirement is yes, you must create a new Power BI table and manage their relationship.
I recently worked with a Power BI Bar chart where the requirement was to sort the bar chart in custom order. For that, I researched some solutions and implemented them in this article.
This Power BI tutorial will assist you with how to custom sort in Power BI bar chart with a simple example.
Power BI Bar Chart Custom Sort
In Power BI, changing the order of a bar chart means adjusting the sequence in which the bars are displayed.
I have a Power BI bar chart with the status order Waiting, In Progress, Completed, Deferred, and Not Started. Refer to the first image.
Now, I would like to sort the bar chart with a custom order like:
- Not Started
- In Progress
Check out the second screenshot below.
We have a SharePoint list (Project List) that contains below columns with various data types:
|Single line of text
|Single line of text
|Single line of text
1. Open the Power BI Desktop and load the data. Then, we can see the data set in the Data pane.
2. Now, under the Visualizations pane, click Stacked bar chart. Then, drag Status into the Y-axis and Product into the X-axis.
This way, we create a Power BI Bar chart.
How to Custom Sort in Power BI Bar Chart
I will tell you how to apply custom sorting in the Power BI Bar chart.
Follow the instructions below to apply custom sorting to the Power BI Bar Chart:
- Create a lookup table in Power BI.
- In the lookup table, Sort by column ID for the Status column in Power BI.
- Using the Status column, manage the relationship between the new lookup table and the original table in Power BI.
- Create a bar chart using the lookup table in Power BI.
Create a Lookup Table in Power BI
Here, we create a new table with columns “ID” and “Status”. The ID for custom order and Status for the relationship between two tables.
1. Under the Home tab, click Enter data.
2. In the “Create Table” dialog box, create ID columns as (1, 2, 3, 4, 5) and Status columns as (Not Started, In Progress, Wating, Deferred, and Completed). Then enter a table “Name.” and click the “Load” button.
This way, we can create a new table in Power BI Desktop.
Sort by Column in Power BI
Now we sort the “Status” column using the simple steps:
- Click the Table view.
- Select the Status column in the new table.
- Click on Sort by column from the top ribbon.
- Select ID.
This way, we can sort columns with the help of another column.
Manage Relationships in Power BI
Now, I want to perform the relationship between two tables using the “Status” column:-
- Click on “Model view.”
- Click the Manage relationships from the top ribbon.
- In the “Manage relationships” dialog box. Click the “New” button for new relationships.
- In the “Create relationship” dialog box. Select the original table and Select the column you want to sort. In my case, it’s “Status”.
- Make sure the “Cardinality” set Many to one. Then, the column has been mapped successfully.
- Click on “OK”.
In this way, we can Manage relationships in two tables in Power BI.
Create a Bar chart using the Lookup table in Power BI
Now, we create a bar chart with simple steps.
1. Under the Visualizations pane, click the Stacked bar chart. Then, in the Data panel, within the Custom List table, drag the Status column into the Y-axis. In the Project List table, drag the Project column into the X-axis.
You can create your custom sort order in the Power BI Bar chart.
I hope you have no doubt about custom sort in the Power BI Bar chart.
In this article, we learned how to create a custom table, manage the relationship between two tables, and change the order of a bar chart in Power BI.
Also, you may like some more Power BI tutorials:
- Power BI Stacked Bar Chart Sorted by Value
- Create a Power BI Pie Chart
- Sort Month Names in Power BI Slicer
- Power BI IF Statement Measure
Preeti Sahu is an expert in Power Apps and has more than 6 years of experience working with SharePoint and the Power Platform. As a Power Platform expert for Power BI, Power Apps, Power Automate, Power Virtual Agents, and Power Pages, she is currently employed with TSinfo Technologies. She is the author of the book Microsoft Power Platform A Deep Dive. She also made a big technical contribution to SharePointDotNet.com in the form of articles on the Power Platform. She enjoys traveling and spending time with her family in her spare time.