Forum Discussion
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):
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.
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
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?