Truly bizarre - adding legend breaks custom sort on X axis

[HM615]

Custom sorted X axis with a ranking table linked to my main data, so I can track a measure across our working year, which starts on week 40 of the calendar year. Works fine in a table. However, on a scatter chart (and a scatter chart only!) the X axis re-orders itself so that it first orders itself according to whether there is a value for the first category of my legend, then, if there isn't, it sticks all those data points at the end. So my X axis, which should run from 40 consecutively to 52, then from 1-39, is in a bizarre order according to whether there is a value there for the first year of my data. It's a mess! Selecting the option to show items that have no data does nothing to change it. It's like the legend categories are somehow superceding the week ranking. Has anyone found a way around this? It's truly bizarre! 

As you can see, the numbers sort-of proceed in the order I want, but where there is no data for my first year (2021/2) those week numbers are thrown to the end. Then it looks like there is a hierarchy, so it puts the next numbers where there is data for 2022/3, but if there isn't throws them to the end of that block. It's absolutely bonkers! Who would ever want a chart to behave this way?!?!?!

[HarishKM]

@HM615  Hello,
 You can follow below steps
1) Adjust X-Axis settings: Go to the X-Axis settings in the scatter chart (under the Format pane) and make sure it is set to show in the order you want, either as Categorical or Continuous,
2) Ensure the Legend is Not Interfering with X-Axis Sorting: Check your legend and see if Power BI is treating the legend categories as a hierarchy. You can try removing the legend from the scatter chart temporarily to see if the issue persists. If it does, it’s likely an issue with how Power BI is combining the X-axis and legend.

3) you could create a new calculated column that combines the week and year, ensuring the order is independent of the legend.

Thanks
Harish M

Did I answer your question? Mark my post as a solution! and Give kudos 

[HM615]

The legend is interfering with the X-axis sorting. That is exactly what is happening. Apologies if I didn't explain it very clearly, but that's exactly the issue - Power BI is treating the legend categories as a hierarchy. I don't know how to make is stop doing that.

Thank you for your suggestion. It would indeed work to combine the week and year into a new column (then write new measures based on that column, bring them in as values rather than as the Y-axis and legend as I'm currently doing, then format them all indvidually into different colours to make it clear) but it seems a heck of a faff to override a feature that has no possible use in any scenario I can imagine! The way I have got round it was to change it to a line chart, instead of scatter graph, and fiddle with the formatting so there are markers but no lines, and it looks like a scatter graph. For no apparent reason, line charts don't treat the legend categories as a hierarchy the way that scatter graphs do. So I have managed to find another way around it - it just seems like such a bizarre setting!

I'm curious if anyone knows a way to turn this off. Still can't understand why anyone would ever want the legend categories to be treated as a hierarchy that overrides the specified sort order! 

[Anonymous]

Hi @HM615 ,
Can you provide some sample data? We can better understand the problem and help you.
How to provide sample data in the Power BI Forum - Microsoft Fabric Community
Or show it as a screenshot or pbix. Please remove any sensitive data in advance. If uploading pbix files please do not log into your account

Best regards,
Albert He

[HM615]

Hello, 

Thanks for the suggestion, but no - that's not it. The week number is already formatted as a number. I tried to switchong to text, to see if that made a difference, but it didn't. It still lists all the week numbers if there is a value for the first variable of the legend, then moves any numbers where there is no value to the end of the scale, ordered according to whether there are values for subsequent variables in the legend. It's absolutely bonkers. 

[Anonymous]

Hi @HM615 ,
Based on your description, I ran some tests and this will make your data cluttered when your week column data type is in text format. But when changed to numeric, it will be in the order of week

Best regards,
Albert He

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