Forum Discussion

Ania26's avatar
Ania26
Helper IV
2 years ago
Solved

Sorting stacked chart

Hello, I have a chart with Others as group which should be at the top always. The rest of the Brands should be sorted by %. Currently I have Others at the top but the rest iis sorted alfabetically. H...
Need Help
  • Ritaf1983's avatar
    Ritaf1983
    2 years ago

    It is possible but, only in a static way.
    It means if you have a slicer for the period for example the sorting order will be the same as for all periods.

    If it's good enough you can apply these steps in PQ :
    1. Duplicate the table

    2. Group new table by category, summarizing the value  :

    3. Sort the table by sum of values in the descending order 

    4. Add index column :

    5. Add a conditional column with some unreal big value for others :

    6. Remove unneeded columns + modify data type of sort column to the whole number and close and apply

    7. create a relationship with the original table :

    8. sort category by sort column from table view :

    9. Drag the data to the Graph with the legend from the sorting table and , modify the sorting from the graph 

    The modified pbix is attached.

    PS

    Sorry I didn't understand right away what you meant to do; the scary stacked chart took up most of my brain power. 🙂 I strongly recommend, though, to share with whoever is requesting you to make it the difficulties in interpreting it, both as a user and as a data visualization expert.

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

Ania26's avatar
Ania26
Helper IV
2 years ago

Hello Rita, Thank you for your reply. I want to sort it by % so for example for 2023 Q2, yellow Others is at the top and then the rest is sorted 2,7% - 3,4% 6,6%. Is this possible?

 

Thank you, Anna

  • Ritaf1983's avatar
    Ritaf1983
    Super User
    2 years ago

    Hi Ania26 , 
    If I understand you correctly and you mean sort every column individually by the values of its parts, the answer is no.

    In addition, while we're on the subject, the chart in the question's image has an issue beyond the technical aspect. Stacked charts with more than two segments can be very challenging for readers because we struggle to compare shapes that don't start from the same baseline. I strongly recommend switching to a small multiple design, which will allow for a cleaner layout and make it easier to compare the trends of each segment over the period represented on the X-axis.

    Something like that :

    And this multiples graphs can be sorted by the values : 


    The modified pbix is attached

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