Power BI Blog: Ordered Legends

Welcome back to this week’s edition of the Power BI blog series. This week, Jonathan Liau will look at ordering the legend in visualisations.

Last week, we created several custom categories with the goal of colouring the current year’s data differently to the previous years.

If we look at the legend, the current order is Mountain Bikes, Mountain Bikes F, Other, Other F,… and so on. What if we wanted the legend to group historical labels together, then forecast (F) labels together? More specifically, what if we wanted the legend to appear on the visualisation in the order illustrated in the following screenshot?

We can order values in the Product Subcategory field by utilising a proxy column that contains numerical values that correspond to the order of the Product Subcategory field:

An idea that comes to mind is to use the SWITCH function to create a conditional column in the Sales table. We can then use that column as the order for the Product Subcategory. Navigating to the Data view of the report, we can create the following custom column in the Sales table:

The code is reproduced here:

This code creates a numbered column that we can use as the order for the Product Subcategory.  With the Product Subcategory column selected, we navigate to the ‘Column tools’ tab on the Ribbon.  Here, we may select the ‘Sort by column’ option on the Ribbon.  This will reveal a dropdown list of all available columns to sort by.  We select the Product subcat order column:

Unfortunately, instead of having the legend reorder on the visualisation, we are greeted with the following error message:

This error message is alluding to a circular dependency between the columns. This is because we are not allowed to create an ‘order’ column based on logic pertaining the column itself.

A workaround that we can adopt is to create a separate logic table (Category Order) in the dataset:

We can then create a relationship between the Category Order table and the Sales table.

With the relationship established between the tables, we can now use the RELATED function to create a conditional column in the Sales table:

Power BI should now allow us to sort the Product Subcategory column by the Product subcat order 2 column.  Navigating back to the Report page, we can see that the legend has now been updated to reflect our desired order: 

That’s it for this week! A quick method of ordering legend or column values in Power BI. Join us next week for more on Power BI.

In the meantime, please remember we offer training in Power BI which you can find out more about here. If you wish to catch up on past articles, you can find all of our past Power BI blogs here.