Scatter plot- add legend removes the X axis ordering

[valcat27]

Hello all,

I created a scatter plot that is defined to be sorted by x-axis, but when I add the legend, it is no longer ordered correctly. 

For a better understanding, imagine that I want to observe the product that each client bought and how much he paid.

These are some information about it that I can share:

- All the fields belong to the same table;
- X axis corresponds to the ProductID and its data type is "Whole Number";
- I have more than 500 products and a ProductID can have up to 8 digits;
- Y axis is a created column that corresponds to the price and its data type is "Decimal Number";;
- Legend is the name of clients and its data type is "Text";
- Each column is defined to be ordered by itself.

Can anyone help me?

Thanks in advance.

[v-yalanwu-msft]

Hi, @valcat27 ;

You could create a column by dax or create a index column in power query as a  auxiliary sorting column. as follows:

1.create a sort column.

sort = RANKX('Table',[ProductID],,ASC,Dense)

2.Select the name field, and then sort by sort column.

3.The final output is shown below: 

Best Regards,
Community Support Team_ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[valcat27]

Hello @v-yalanwu-msft, 

Thank you for your help, but it did not work because I have more than one Client per Product. 
I tried with my data and I got this message: It's not possible to sort the column "ClientID" by "sort". It cannot exist more than one value in "sort" for the same value in "ClientID". Choose a different column to sort or update the data in "sort". 

[v-yalanwu-msft]

Hi, @valcat27 ;

According to your description, I tested it which one product have three different client . then use same ways sort ,and it is ok.

The final output is shown below:

I am sorry that i can't reproduce your error, however , you also could try to create a index column as a sort column in power query .
1.sort by ProductID

2.Add index column as a sort column.

3.sort by index column.

If it doesn't work, can you share the picture of your data or pbix whitout any sesentive information and the result you want to output? 

Best Regards,
Community Support Team_ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[valcat27]

Hello @v-yalanwu-msft ,

Thank you again for your help. 

Creating the index column, I got the same message error. I am sorry, but I noticed that I misinterpreted it. The problem is that the same ClientID can have more than one product. So when I choose to sort ClientID column by sort/index column, that client will have more than one value associated and probably the program will not know which value to choose to apply the sorting. 

[v-yalanwu-msft]

Hi, @valcat27 ;

Sorry, it is difficult to reproduce your mistake, can you share your file whitout any sensitive  information?

[valcat27]

Hello @v-yalanwu-msft ,

The error message is the same. I created a new table (Table2) with duplicated clients in your pbix. I think that if you try to sort ClientID column by sort/index column, you will get the message that I referred. 

I'm not able to attach the pbix. I tried to paste it and I got the error: "The file type (.pbix) is not supported."

This is the table with my changes: 

ProductID	Price	ClientID
12244	13	f
12334	53	f
12345	16	ce
12345	17	be
12345	8	ce
13234	12	a
13445	45	sd
13455	21	dw
45566	13	a

[v-yalanwu-msft]

Hi, @valcat27 ;

According to your data,because your data have same ClientID but different ProductID, so  it can’t identify by which ProductID the same ClientID（such as two f) should be sorted? 

My idea is create a ClientID2 column which make the two f change to "f" and "f " respectively, and add a space to distinguish the different keys. In you simple example.I create a column as follows:

ClientID2 = var _count=CALCULATE(COUNT([ClientID]),ALLEXCEPT('Table','Table'[ClientID]))
return IF(_count=1,[ClientID],IF([ProductID]=CALCULATE(MIN([ProductID]),ALLEXCEPT('Table','Table'[ClientID])),[ClientID],[ClientID]&" "))

The final output is shown below:

Of course, if you have three or more same ClientID but different ProductID, you only add more space to distinguish it.

Best Regards,
Community Support Team_ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[valcat27]

Hello @v-yalanwu-msft ,

Thank you for your effort and I am sorry for my delay but I could not properly analyse your answer before. 

From what I understood, as I can have thousands of ProductID's for the same ClientID's, I would have to adapt your formula and probably create some variable counter (I do not know...).  Nevertheless, I think that adding the legend will not be effective because I will have so many colours for the same ClientID that it will be hard to analyse the visual. 

[v-yalanwu-msft]

Hi, @valcat27 ;

I am not very clear about your meaning and scenario, can you share similar screenshots or more information about your question? After my test, if the X-axis and Y-axis are of Whole Number and Decimal Number, it is automatically sorted according to the size of the number,such as the below:

If you change the ProductID type to "text", the sort and the chart will change also.

Best Regards,
Community Support Team_ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[valcat27]

Hello @v-yalanwu-msft ,

Thank you for your answer.

Defininig ProductID as text, it does not work because it sorts it considering only the first digits and ignoring how many digits the number has. It sort is like this: 12345, 124,15, 2357, 274...

With the productID as Whole Number type, I selected the type of the x-axis as categorical to avoid overlapping values. As you can see in the following figure, the values are not all sorted. It looks like they are sorted for some groups/periods. (The visual was selected to be sorted by ProductID and ascending).