Stacked graph sort order

[andrerosario]

Hi!

I have the attached stacked column graph.

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

 

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é

[jiri_novot]

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]

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

[KGrice]

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]

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 · Accepted solution]

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

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

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.

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.

[JoeP]

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? 

[andrerosario]

You rock!

Thank you very much 🙂

[KGrice]

You're welcome @andrerosario!