Sorting stacked chart

[Ania26]

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. How to change it?

[Ania26]

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]

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

[Ritaf1983]

Hi @Ania26 

This is a question that's hard to answer without understanding the basis for the groups, so I'll try to explain the sorting logic in general terms.

In order to sort the graph in the desired order at the category level, including the address, there needs to be a table that holds the sorting logic. This sorting logic table can be built in Power Query (PQ).

To create a sorting table, I will load it into Power Query (PQ), remove duplicates, and delete all columns that are not categories.

From here, we need to understand what this means in terms of sorting order, given that "OTHERS" starts from the top. Since the graph starts from 0, the order should generally be from bottom to top. However, I have a feeling that for you, the order might actually be from top to bottom, so I prepared two sorting tables with two different logics.

On the side of each table in Power Query, you’ll find the steps I took to achieve the sorting order.

After closing and applying in PQ you need to modify the sort by the column that has been created in PQ:

**Of course, you won’t need two tables like I set up because it was just to cover both sorting options since I didn’t know exactly what is the needed order.

Now you can create a graph with the category in the legend from the sorting table.

Results:
Descending ("a" on top under the others):

Ascending  ("a" on the bottom):

The pbix is attached, you can follow the steps 

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

[Ania26]

Hello, thank you for your effort.  I was asked to make exactly the chart as I sent in the first message with yellow OTHERS at the top and rest sorted by % ascending, it should be in the stacked chart. I am not sure if this is evev possible in Power BI.

Anna

[Ritaf1983 · Accepted solution]

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]

Hello Ritaf1983, thank you for your help.