Forum Discussion
Sorting stacked chart
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 table2. 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
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
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
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 table2. 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
Hello Ritaf1983, thank you for your help.
