Modify the legend order

Hi megm001,

There are a few options to explore here depending on your data, so I'll walk through these step by step. After my previous post in which I described using the Group feature to enable sorting by adding a numerical or alphabetical value ("A:", "B:", "C:" or "1:", "2:", "3:", etc.) to the front of each category, I've since started using the Sort by Column feature which I've found to be much cleaner since it doesn't force you to add some additional number or letter to the attribute values in your legend (which can be a bit distracting). It also passes through the sorting to all your visuals, so you don't have to worry about managing this at the visual level.

Let's look at an example.

Suppose I have a very simple table with two columns, one an ID and the other a Likert score ranging from 1 to 10 inclusive (for this example, I created the table in Excel and connected to the workbook using Power BI). Here's what the table looks like.

I then created a very simple column chart visual to show the distribution by score.

As in the issue you mentioned, 10 appears after 1 instead of appearing at the end after 9. In this example, all of the values stored in the Likert_Score column range from 1 to 10, so there is actually a very simple fix for the sorting issue. All you need to do is go to the query editor, right click on the Likert score column, and update the column data type from text to whole number. The sorting should then update automatically. See below.

Fixed! There's only one problem. This approach requires converting the column to a numerical data type, but what if your column can't be converted from a text to numerical data type? For example, suppose your column contains "N/A" values (which would throw an error) or isn't even numerical in nature. Let's say for example you have a column with planet names in it and you want those planets to appear in order of increasing distance from the sun instead of alphabetically (i.e., Mercury, Venus, Earth, Mars, etc., instead of Earth, Jupiter, Mars, Mercury, etc.). The good news is there's an easy way to do this which involves setting up a very basic dimension table. Going back to the Likert example, let's say we have some observations that are "N/A", which gives us the following distribution.

Notice that N/A is showing up where we might want it to at the end, but 10 is back in the wrong spot and we can't fix that by changing the data type, because the column now contains non-numerical values. So, let's create a dim table that we can use for sorting. To do this, start by going to Home and selecing Enter Data.

You can then key in a table with two columns, one containing a unique list of values appearing in the original Likert_Score column and another containing the order in which you want those values to appear in the visuals. Then give your table a name and click Load. Note that you could also create this table in a separate Excel workbook and connect to it from Power BI (which works just as well).

Once you select Load, your new Dim_Sort table should appear under the Data section.

Next, we need to set up a relationship between Dim_Sort and the original table containing our Likert scores.

If you gave the column containing the unique list of Likert scores in your new Dim_Sort table the same name as the column containing the Likert scores in your original data table, Power BI should be able to automatically detect the relationship. Let's assume it doesn't though, and we have to set the relationship up manually. Here's what that would look like. Enter the information as shown below, and hit Load.

Once you establish the relationship, you should see it under the Relationships section as shown here.

Next, we need to join this new Sort_Order column to our Likert Scores table so we can use it for sorting the Likert_Score column. To do this, go to the Data section, then select the Likert Scores table, Modeling, and Add Column. Enter the DAX formula shown below (you'll need to update the table column names to reflect your actual data). Once you finish entering the formula, the sort order values from Dim_Sort should now appear as a new column in the original table containing your Likert scores.

Now, all we need to is sort the Likert_Score column by the new Sort_Order column. To do this, select the Likert_Score column, then go to Modeling, Sort by Column, and selec the Sort_Order column.

That's it! All your visuals should now reflect the correct sort order.