Adding a legend to a Scatter Plot disrupts the chronology of the X-axis

[Riteman]

Hi

I have a Scatterplot based on a large table where each line contains a product Code, a description, the amount (Quantity) of products on that line, the Date they are supposed to ship (always the first day of a month) etc.

When I build the scatterplot using Shipping date as X-axis and Sum of Quantity as Y-axis the Visual looks correct:

The X-axis starts "now" and ends at the end of the year 2025.

However as soon as I add the product Code to Legend in order to get an overview showing when the different types of products will ship, the X-axis gets mixed up. I have Googled a lot and found that I am not alone having this problem:

I tried adding an Index column to be used as the sort by column but in vain.

The problem is that the same Product is found in many months over a year.

It should look like this BUT with the X-axis starting "now" and ending 12 months later:

I really hope some bright people have a solution to that problem as it is driving me crazy - I know the reason, I believe that if I could have the code (product ID) ordered as the dates it should work but how?

[Riteman]

There are several people asking the same question on this Forum but till now I have not found any solution - have a look here https://community.fabric.microsoft.com/t5/Desktop/Scatter-plot-add-legend-removes-the-X-axis-orderin...

I have uploaded my datafile and a PBIX-file and some screenshot showing the drastic changes on the X-axis as soon as you add a Legend.

In the Post mentioned here it was suggested that th ereason was thta there were identical Legend values so I analyzed my data: There are a maximum of 7 identical Legend values per month: As all my Dates are on the first of a month I found 

the duplicates and used them to modify the dates of the Product (the Code). Thus first duplicate is palced at the 1.st of a month, the 2nd duplicate on the 2nd of that month etc etc. Using these modified Codes did NOT solve the problem.

I then modified the Codes so that I concatenated the original Code with the modified Date code (1.1.2025, 2.1.2025..) so that no Code can be identical but using that modified Code as Legens also does not solve the problem.

Can it be the amount of Legends which breaks Power BI??

[Syk]

Your x-axis is formatted as a date, correct? I'm trying to replicate your issue without much success.

Before:

After:

[Riteman]

Hi

my guess is that you have very few Entities?

I did a lot of tests and my conclusion is as follows:

If you have few values as Legends you can have a Categorical X-axis (if I use my Probabilities which can only take on the values 0.5, 0.6, 0.7, 0.8, 0.9 and 1 the Scatterplot works OK.

If I use the Statnumbers or the Codes, the X-axis gets mixed up - you cannot have a Categorical X-axis when the number of Legends increases. 

I tested a few other solutions from companies like xViz and the first thing I noticed is that their Scatterplots only take numerical values as Axis 😉

I then changed Month from Jan - Dec into Numbers 1 - 12 and used them as X-axis and Bingo the Scatterplot works without any issues.

Unfortunately it does not show all 12 values (only every second value) so I changed the setting of the X-axis to Categorical and guess what happened? The X-axis got mixed up!

[Riteman]

Hi

could you share your files so that I can see the difference between the two? I tried to use another Legend (Statnumbers) but as soon as I add a Legend teh X-axis is ruined.

[Riteman]

Yes

I really appreciate your help!

I will send you data (seems there is no way to upload a file here?) so that you can try it out - it will not take long:

Create a Scatter plot, Choose Expected date of Delivery as X Axis and Quantity as Y axis. That will show a nice Scatter plot which starts November 2024.

In order to show the different products, you then add Code to Legend and the X-axis is ruined.

You probably need a large number of Codes for that to be visible.

[Riteman]

 

 

 

 ..and here a link to Google Drive where you will find the datafile and the PBIX file https://drive.google.com/drive/folders/13ur3KCtRx6Y600A2lt3OJH09o9AmNK5y?usp=sharing

[Syk]

Have you checked this yet?

[Riteman]

Thanks but sorting as indicated does not solve the issue - data are well sorted until the Legend field is used. I found a Post (https://community.fabric.microsoft.com/t5/Desktop/Adding-A-Legend-Disrupts-Chronological-Order-of-Da...) discussing the same problem from 2022 but the solution does not work if you have plenty of identical Values spread over the date axis:

The screenshot is from that post but I get the same error when trying the outlined solution using my data.