Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Line Chart sort x-axis by y-axis

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?

Tips and Tricks
Tutorial Requests
  • Icey's avatar
    Icey
    6 years ago

    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.

11 Replies

  • Icey's avatar
    Icey
    Community Support
    6 years ago

    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's avatar
      Anonymous
      Not applicable
      6 years ago

      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's avatar
        Icey
        Community Support
        6 years ago

        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.