Line Chart sort x-axis by y-axis

[Anonymous]

Hi 

I have a table consiting of Sellers, MFGs and Partnumbers.  It looks like so:

_____________________________

Id1 | Seller1  | MFG1 | Part1

Id2 | Seller2  | MFG2 | Part1

Id3 | Seller3  | MFG1 | Part2

I would like to create two charts:

• Showing the number of destinct MFGs each Seller has 
• Showing the number of destinct Part Numbers each Seller has

Just inserting MFGs into value and Counting Destinct creates and inserting Id into Axis creates this graph which:

The problem here is that it is not sorted by the values on the x-axis. 

I have seen that it is possible to change the sorting on charts. However for some reason that is possible for me:

Here is a chart of the number of destinct MFGs per seller. This is how it should look.

In order to sort the graph correctly I created a new table where I calculated the number of Destinct MFGs, Sorted in Descending order and inserted an Index colum.

This works fine for a single or two differnt charts. However when I do this for 3 or more different charts the calculation time goes from 1 minute to 30+ minutes on a dataset of 200MB. I think this long calculation times stems from referencing my FactTable as seen here: https://docs.microsoft.com/en-us/power-bi/guidance/power-query-referenced-queries

So my question is: Are there any other wayt to sort a graph besides creating a new table with new calculations?

[Icey]

Hi @Anonymous ,

Please check whether this is what you want:

Change the Type of X aixs from "Continuous" to "Categorical".

Best Regards,

Icey

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

[Anonymous]

@Icey This definitely sorts the graphs correct. 

However, this results in a scrollbar at the bottom, because there are too many sellers to show at once. 

I'm working with 1+ million sellers, so having a scrollbar result in the graph being unmanageable, and I can't get the full picture.

[Icey · Accepted solution]

Hi @Anonymous ,

How about add a Rank column in your table, like so:

Rank column =
RANKX (
    'Table',
    CALCULATE (
        DISTINCTCOUNT ( 'Table'[MFG] )
            + SUM ( 'Table'[ID] ) / 10000,
        ALLEXCEPT ( 'Table', 'Table'[Seller] )
    ),
    ,
    DESC,
    DENSE
)

Then, create the bar visual with "Rank column".

Best Regards,

Icey

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

[Anonymous]

Hi @Icey 

What is the reasoning behind 

SUM ( 'Table'[ID] ) / 10000

And why is the sum divided by  10000?

It does not work for me.

Using this code: 

Rank column = 
RANKX (
    'BaseData';
    CALCULATE (
        DISTINCTCOUNT ( 'BaseData'[MFG] )
            + SUM ( BaseData[Index] ) / 10000;
        ALLEXCEPT ( 'BaseData'; 'BaseData'[Seller] )
    );
    ;
    DESC;
    DENSE
)

[Icey]

Hi @Anonymous ,

For your question, it is used to re-sort the same sorted sellers. For example, Seller 1 and Seller 2 rank the same, both are the 3th. In order not to "sum" the two values on the visual, seller 2 is ranked 3th and seller 1 is ranked 4th.

Then, please let me know if the "Index" column in your table is the ID of "Seller"? If not, please correct it.

It is best to give me some sample data (not real data) and keep it consistent with your real data structure.

Best Regards,

Icey

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

[Anonymous]

Hi @Icey 

My index column was not correct. However, correcting it, resulted in the same.

I have created a small dataset with 475 sellers you can use. It can be found here: https://drive.google.com/file/d/1aBXWKbupO4crg0DRa68G4v83YMraSi4-/view?usp=sharing

[Icey]

Hi @Anonymous ,

I have tested with the sample data you provided, and it works well. So weird.

BTW, .pbix file attached.

Best Regards,

Icey

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

[Anonymous]

Hi @Icey 

It was because the 10k in your examples was too low and it was still summing multiple Sellers MFG count together so Increasing that number solved my issue.

Thank you very much this is gonna save me so much time 😄

Also, @Anonymous could you add that changing the 10k number might work for large numbers of indexes to the post I added as the solution? 

[Icey]

Hi @Anonymous ,

Do you mean that how to add one "Index" column?

Try this:

Index Column = RANKX(IdSellerMFG,IdSellerMFG[Seller],,ASC,Dense)

Best Regards,

Icey

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

[Anonymous]

Hi @Icey 

No, I was thinking of the line:

SUM ( 'Table'[ID] ) / 10000

[Icey]

Hi @Anonymous ,

Thank you for your reminding, indeed, when encountering large indexs, this may be problematic. You can adjust according to your actual data.

Best Regards,

Icey