legend custom sort not working

[Anonymous]

I have a line chart with legend field [FY Label], whose values are like '2018-2019', '2019-2020', 'Current FY'.  This field is a text data type.  So, by default, the legend sorts it alphabetically, in ascending order (i.e., the order that I listed).  However, I want it to be custom sorted like the following: 'Current FY', '2019-2020', '2018-2019'.  I have another field in my table called [FY Numeric], with values like 2020 (corresponding to 'Current FY'), 2019 (corresponding to '2019-2020'), and 2018 (corresponding to '2018-2019').  So, in the Data view, I sort field [FY Numeric] in descending order (to put the current FY in the first position of the legend sort), and I sort the [FY Label] field by the [FY Numeric] field.  However, my visual still shows the legend with the default sorting.  How can I resolve this?  I have tried re-creating the visual and even sorting the [FY Label] field by itself (in both orders), neither of which changes the sort order in the visual.  I have used this solution (create custom numeric sort field, sort my desired field by this field) in other visual types before, and it has always worked.  I'm wondering if this doesn't work on line charts.

[v-lionel-msft]

Hi @Anonymous ,

The only way is to add such a [Index] column (you can use M or DAX to create such a column), then sort the [FY Label] column by the [Index] column.

Best regards,
Lionel Chen

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

@v-lionel-msft 

That's precisely what I did.  Why is it not working?  Is this a bug?  I've even tried changing the visual to something else (e.g., a stacked bar chart -- to rule out the issue being visual type specific), and it's still not working.

[edhans]

This is not a bug. Have you carefully read the SOrty by Column article from Microsoft? In every case were it didn't work, someone wasn't doing one or more steps. 

If you can share your PBIX file (no confidential data) we can take a look at it and assist.

[Anonymous · Accepted solution]

I just figured out what I did wrong.  It turns out that the sort order of the "index" / numeric column used for the "sort by" operation in legends will always be ascending.  That can't be controlled.  Sorting the "index" column in the Data view does nothing.  So, since I want 'Current FY' to appear first, I have to change what values I assign in the [FY Numeric] column.  Having 'Current FY' correspond to '2020' and '2019-2020' correspond to '2019' etc. is the wrong order.  I need to do something like '1' for 'Current FY', '2' for '2019-2020', etc.  So, the trick will be to figure out how to make this dynamic.  That is, rather than hard-coding '1' for 'Current FY', I need to dynamically detect the current FY (and assign it value '1').

[edhans]

Just make the index negative. so 1, 2, 3 becomes -1, -2, -3. Now -3 is the first thing and will sort ascending. Would work with years too. -2020 is smaller than -2019.

[edhans]

On the chart itself, click on the elipses and make sure you are sorting by the correct field. If you are sorting by sales or something else, it won't sort by the Sort by column. The Sort by column only works that way when that field is the column you are sorting by.