cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Kane
Helper I
Helper I

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
Sean
Community Champion
Community Champion

@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

15 REPLIES 15
mvarnado
New Member

The above solutions are valid, but I wanted to share my simple method.  YMMV.

 

I have a table of change tickets, and I want to sort them by Risk Level.  The values are Low, Medium, High, and Very High.  I want to have a stacked column in this order.  However, they list alphabetically and stack High, Low, Medium, Very High.

 

*  Create new column called "Risk Sort Order"

             RiskSortOrder = SWITCH('Change Tickets'[Risk Level], "Low", 1, "Medium", 2, "High", 3, "Very High", 4)

 

At this point, if you try to do "Sort By" on the Risk Level column by Risk Sort Order, you get the error that a column can not be sorted by a column it directly or indirectly affects (etc...  didn't screen shot it).

 

* Create another new column called "Risk Level Push" which will be a direct copy of the original Risk Level column

           RiskLevelPush = 'Change Tickets'[Risk Level]

 

* Select Risk Level Push column, Sort by Risk Sort Order

 

* Change Visual Legend to use Risk Level Push, rename at visual level "Risk Level"

 

And Viola!  Stacks are now in order as desired - Low, Medium, High, Very High.

 

 

This method works great when you have a small, finite number of legend items that you can predictably assign values.  Obviously, it's not going to work in a dynamic value situation.

 

pratk30
Advocate I
Advocate I

megm001
Helper I
Helper I

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!

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 

 

 

Thank you! This is perfect!

stefan_friedel
Advocate I
Advocate I

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

 

Hi Stefan,

 

As per u i have created group but the problem is in the stacked column chart i am calculating the percentage of each category when compared to all categories.If i create a group as u mentioned i will get 100% for all the catgories.How to resolve this ?

 

2.PNG

Stephan's solution looks perfect to my situation, but I don't see the "New group" option if if select the arrow. I only see the options "Delete field", "Change name" and "Show items whithout data". Has the "New group" option been deleted or am I looking at the wrong place?

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

Sandy0007
Microsoft
Microsoft

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

Sean
Community Champion
Community Champion

@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

mmena
Regular Visitor

Can someone provide an example of this Measure?

Abhilash241
Frequent Visitor

Hi,

 

U can place only one measure in values area ,if you create 10 measures for each status how will you put this 10 measures in value ?

 

Please suggest

 

Thanks

@Kane

I would like to offer other web visitors what I think is an even better invisble solution in some circumstances, which is to prefix your text with a number of unicode 'zero width space' characters (https://www.fileformat.info/info/unicode/char/200B/index.htm).

 

Each of these characters has a code, and PowerBI will honour it in its default sorting, but the characters are not visible. Therefore you can add as many as you like. The more you add, the earlier the string will sort.

 

In my case, I do this in the SQL view definition which is the source of data for PowerBI e.g.

 

SELECT CASE [somenvarcharfield] WHEN 'alpha' THEN NCHAR(8203)+'alpha' WHEN 'beta' THEN...... 

 

e.g. producing the following strings in SQL...

NCHAR(8203)+N'alpha'

NCHAR(8203)+NCHAR(8203)+N'beta'

NCHAR(8203)+NCHAR(8203)+NCHAR(8203)+N'gamma'

 

Will produce the visible sort order in PowerBI...

gamma

beta

alpha

 

It may be possible to inject these unicode characters straight into the PowerBI user interface but I haven't tested this - the above works well for me and I generally stick to doing as much work in the view (rather than in PowerBI) as possible.

 

I hope this helps someone else.

 

Tim

Thank you @Sean !

This is really an excellent solution !!!

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

Power BI October Update 2021.jpg

Power BI Release

Click here to read more about the October 2021 Release!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

Teds Dev Camp Oct. 2021 768x460.jpg

Power BI Dev Camp - October 28th, 2021

Mark your calendars and join us for our next Power BI Dev Camp!