cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
andrerosario Frequent Visitor
Frequent Visitor

Stacked graph sort order

Hi!

 

I have the attached stacked column graph.

How can I define a specific order for the columns and for the "series"?

 powerbi.PNG

As you can see, the columns are "9; 8_A; 8;11;10", as I have an "8_A", which is not a number, I can't sort by number... is it possible to define the order taht I want?

Tha same for the "series"...they are arranged by alphabetical order from bottom to top and I want to change that order. How?

 

Thank you!

André

1 ACCEPTED SOLUTION

Accepted Solutions
KGrice Established Member
Established Member

Re: Stacked graph sort order

The good thing about adding a column in M (Edit Queries) or DAX (Data view) is that once you add the column, it will be there for any new data you import when you refresh the report. As long as you're refreshing and not starting your entire Desktop report from scratch every time, any new columns will be there.

 

However, you can also do this with the related table. If your main table looks like this (the Scores column is just something to add data in there for the chart later):

 

MainTable.PNG

 

And your Aux table has unique values and the desired sort order like this:

 

AuxTable.PNG

 

You can create a relationship between them on the Column Values column. Power BI will probably have already created it for you, really. Check in Manage Relationships in the Home tab of the ribbon.

 

Relationship.PNG

 

With that in place, go to the Data view and add a New Column to the main table.

 

Sort Order = RELATED(AuxTable[Sort Order])

 

Use that new column with the Sort By Column feature, and you're good to go. Notice that 8_A in the chart below falls between 8 and 9, based on the sort order provided in the table. Of course, you're still adding a new column with this method, but again, it will be there when you refresh.

 

Chart.PNG

View solution in original post

8 REPLIES 8
KGrice Established Member
Established Member

Re: Stacked graph sort order

You can use Sort By Column in the Modeling tab of the ribbon, while in the Data pane on the left-hand side. This will allow you to have one column's values define how a different column is sorted. You'll first need to add a column that defines your order. Check out this post for details on how to do this.

andrerosario Frequent Visitor
Frequent Visitor

Re: Stacked graph sort order

The thing is that I would prefer not to add any column to set that order (as my report is based on some excel files and each time I would receive a new updated version, would have to insert that same column...)

Can it be done based on an aux table, linked to this one?

 

Example:

Aux table with

8_A - 1

8 - 2

9 - 3

 

Select the column on the original file and sort by the column with the order on the aux table.

Is it possible?

 

Thank for your help!

KGrice Established Member
Established Member

Re: Stacked graph sort order

The good thing about adding a column in M (Edit Queries) or DAX (Data view) is that once you add the column, it will be there for any new data you import when you refresh the report. As long as you're refreshing and not starting your entire Desktop report from scratch every time, any new columns will be there.

 

However, you can also do this with the related table. If your main table looks like this (the Scores column is just something to add data in there for the chart later):

 

MainTable.PNG

 

And your Aux table has unique values and the desired sort order like this:

 

AuxTable.PNG

 

You can create a relationship between them on the Column Values column. Power BI will probably have already created it for you, really. Check in Manage Relationships in the Home tab of the ribbon.

 

Relationship.PNG

 

With that in place, go to the Data view and add a New Column to the main table.

 

Sort Order = RELATED(AuxTable[Sort Order])

 

Use that new column with the Sort By Column feature, and you're good to go. Notice that 8_A in the chart below falls between 8 and 9, based on the sort order provided in the table. Of course, you're still adding a new column with this method, but again, it will be there when you refresh.

 

Chart.PNG

View solution in original post

andrerosario Frequent Visitor
Frequent Visitor

Re: Stacked graph sort order

You rock!

 

Thank you very much 🙂

KGrice Established Member
Established Member

Re: Stacked graph sort order

You're welcome @andrerosario!

jiri_novot Visitor
Visitor

Re: Stacked graph sort order

Hi KGrice,

I would like to ask whether you would have an explanation why changing sort column is not applied to the data in charts. I tried to follow the steps in your reply/solution and even reproduced the very simple case here but I still cannot have my data sorted by the column selected in the Data view. I do see the table in the Data view sorted by the column I select but the visual (stacked column chart) always defaults to sorting by the column on the x-axis.

My data come from an Excel sheet and go through some shaping and transformation. I used the sort order column injection from the related table that defines the whole-number column to sort by. To be more specific, I use the calendar week number at the x-axis and want to start at the calendar week 40/2016 (start of the fiscal year ), ending at the calendar week 39/2017 (end of the fiscal year). I want to have the calendar week displayed at the x-axis but sorted by the date ascending. That would be cw 40, 41,..., 1, 2,..., 39. By now, it always defaults at cw 1, 2, ..., 52 which is not correct. I simply cannot force it to use the sorting column in the Data and ran out of ideas what I am doing wrong.

 

Any help will be much appreciated. Thank you.

 

Best regards

Jiri

 

 

swong Regular Visitor
Regular Visitor

Re: Stacked graph sort order

@KGrice, I have the same question as @jiri_novot

 

My data is sorted, but the order of stacked colors is still alphabetical, instead of the order I specific in my aux table. Any thoughts on why this is?

JoeP Visitor
Visitor

Re: Stacked graph sort order

I have the same problem as andrerosario but can't understand KGrice's solution. Would someone be willing to unpack this step by step for a novice? 

 

 

Helpful resources

Announcements
Exclusive LIVE Community Event #3 – Ask Arun Anything

Exclusive LIVE Community Event #3 – Ask Arun Anything

Join us in the third Triple A event!

Meet the 2020 Season 1 Power BI Super Users!

Meet the 2020 Season 1 Power BI Super Users!

It’s the start of a new Super User season! Learn all about the new Super Users and brand-new tiered recognition system.

January 2020 Community Highlights

January 2020 Community Highlights

Make sure you didn't miss any of the things that happened in the community in January!

Top Solution Authors
Top Kudoed Authors