cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Kane Regular Visitor
Regular Visitor

Modify the legend order

Hi, PowerBI experts,

 

Now, I would like to summarize the count of a column named "tests" with a stacked column chart. And I also take the column "status" as the legend.

 

From the picture below, you can find that the "Blocking" part is at the bottom of the chart.

My question is: how to move the "Blocking" part to the top of the chart ?

In other words, how could we custom the display order of the legend ?

legend order.png

 

THANKS !

 

Kane

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Sean Super Contributor
Super Contributor

Re: Modify the legend order

@Kane

You have to create Measures for each type of Status

 

Then place each Measure in the Values area in the order you wish

 

I responded to a similar question but for a pie chart today - it works exactly the same way with a bar chart

 

http://community.powerbi.com/t5/Desktop/Rearrange-Pies-in-Pie-Chart/m-p/25218#U25218

View solution in original post

12 REPLIES 12
Highlighted
Sean Super Contributor
Super Contributor

Re: Modify the legend order

@Kane

You have to create Measures for each type of Status

 

Then place each Measure in the Values area in the order you wish

 

I responded to a similar question but for a pie chart today - it works exactly the same way with a bar chart

 

http://community.powerbi.com/t5/Desktop/Rearrange-Pies-in-Pie-Chart/m-p/25218#U25218

View solution in original post

Kane Regular Visitor
Regular Visitor

Re: Modify the legend order

Thank you @Sean !

This is really an excellent solution !!!

Sandy0007 Occasional Visitor
Occasional Visitor

Re: Modify the legend order

Unable to Edit legend according to my criteria can you help me with it

stefan_friedel Frequent Visitor
Frequent Visitor

Re: Modify the legend order

@Sean@Kane

 

I came across another way to do this that doesn't require the creation of new measures. Here are the steps:

 

1) Select your visual.

2) In the Legend pane, select the arrow next to the field that you want to reorder.

3) Select New Group.

4) Create a name for your group (Power BI will populate one by default).

5) Use the group dialogue to create a separate group for each attribute value that you want to appear separately in the visual. You can group attribute values or keep them separate by creating a group for each one. There's also a handy "Include Other group" option that groups any remaining attribute values into an "Other" category if you don't need them broken out.

6) Since Power BI orders the legend alphabetically, if you rename the groups and place a number in front of each one indicating the order in which you want it to appear ("1)" or "1-" or "1:" for example), Power BI will display the groups in ascending order based on the leading digit.

 

Power BI also allows you to reuse the grouping you created in other visuals. It's a really handy way to reorder the attributes if you don't mind there being a number in front of each item in your legend.

 

Re: Modify the legend order

Despite the fact that Stefan answer was not tagged as the correct one, i think his is the most professional and accurate answer. Thanks!

megm001 Regular Visitor
Regular Visitor

Re: Modify the legend order

Hi!

I have a similar issue, where I have data from a Likert scaler (1-10) and the legend orders my values as: 1, 10, 2, 3, ...

Can you show me, step-by-step with sample formulae and pictures, what I need to do to order it as: 1, 2, 3, ...?

Thank you!

stefan_friedel Frequent Visitor
Frequent Visitor

Re: 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.

 

1.png

 

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

2.png
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.

 

3.png4.png5.png

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.

 

dist.png

 

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.

 

6.png

 

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

 

7.png

 

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

 

8.png

 

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

 

10.png

 

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.

 

13.png

 

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

 

16.png

 

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.

 

18.png

 

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.

 

22.png 

 

 

megm001 Regular Visitor
Regular Visitor

Re: Modify the legend order

Thank you! This is perfect!

pratk30 Regular Visitor
Regular Visitor

Re: Modify the legend order

Helpful resources

Announcements
Can You Solve These Challenge

Challenge: Can You Solve These?

Find out how to participate in the first Power BI 'Can You Solve These?' challenge.

Community News & Announcements

Community News & Announcements

Get your latest community news and announcements.

Virtual Launch Event

Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

Community Kudopalooza

Win Power BI Swag with Community Kudopalooza!

Each week, complete activities and be qualified in the drawing for cool Power BI Swag.

Users Online
Currently online: 138 members 1,719 guests
Please welcome our newest community members: