X-axis sort order breaks when adding legend in scatter chart

[ed-freeman]

Hi there,

I'm trying to use a scatter chart with a categorical x-axis (i.e. it's more of a dot-plot than a scatter chart). I have sorted the values in my x-axis by an "order" column like below:

When I apply it to the x-axis on my scatter chart, it's fine:

However, when I add certain fields as a legend to that chart, the order sometimes becomes out of whack somehow:

Does anyone know why this may be happening?

Thanks,

Ed

[v-qiuyu-msft]

Hi @ed-freeman, 

Please share some sample data of fields used in last screenshots. Also please clarify your desired results based on these sample data. 

Best Regards,
Qiuyun Yu

[ed-freeman]

Hi @v-qiuyu-msft,

Thanks for your response, and sorry for the slow reply.

I figured out the cause of the issue: when you add a legend to the scatter chart, the field in the legend has an affect on the scatter points whereby it seems like each value in the legend field is ordered separately, in addition to the order specified by the field on the x-axis.  

I realise that this isn't easy to explain in writing, so here's a pbix file to show what's happening. The x-axis has the field "Level" which is either "Low", "Medium" or "High". The y-axis has a dummy aggregator for a field "Target Level", which is used to plot the "Level" against the "Target Level". The correct order for the x-axis is "Low", "Medium", "High", which has defined in the data model.

Before adding the legend, the sort order works - it is always in the order "Low", "Medium", "High". However, when adding a "Group" field to the legend, the sort order goes to "Low", "High", "Medium".

Single selecting a "Group" on the filter pane fixes things, but when no "Group" (or all Groups) is selected, the sort order goes awry.

I think it's something to do with the fact that there isn't data in the fact table for all combinations of the legend field and the x-axis field (imagine you were to do a cross-join between those fields). This leads to the apparent behaviour where the x-axis sorts differently depending on the combination of legend values selected.

Is this expected behaviour?

Hope this helps,

Ed

[v-qiuyu-msft]

Hi @ed-freeman, 

Please select the scatter chart visual, go to Format pane -> Y axis tab, turn on "Show blank values" option. 

Best Regards,
Qiuyun Yu

[ed-freeman]

Hi @v-qiuyu-msft ,

Thanks for the idea!

That could be a solution, but in my case I don't actually want to "show" blank values. I think that could confuse the end-user.

Is there any way to keep the correct x-axis sort order, without having to "Show blank values"?

Thanks,

Ed

[AWolvan9]

Thanks @v-qiuyu-msft ,

Allowing blank values fixed the sorting issue for me. As Ed also commented would still be interesting to see how to resolve this without having to do this.

It seems like a very strange behaviour, and one that surely cannot be intentional in PowerBi's design.

Thanks,

A

[Teaman]

Hi All,

I think I've found a way around this issue. If you switch the scatter graph to a line graph (keeping axis, details and legend data fields the same), you can remove the lines and add in the data points without the x-axis distorting. 

In the line graph, navigate to Format > Shapes and change the stroke width to 0 and enable 'Show Marker' or 'Customise Series' to show and customise the data points.

The only issue with this is I don't think you can change the size of the data points to show another column/ measure like in the original post. 

Thanks