Forum Discussion
Sorting a stocked column chart
Hello,
I have an issue with the sorting of this stocked column chart.
My chart is currently sorted by the values of the x-axis which are the country and the year (the concatenate label is turned off, each color represents a country). What I would like to do is to sort this chart by the values of the bars (e.g. Sales) per country. That means that I want to have on the left of the chart, the country with the highest sales but still with the same structure (country: 2018 2019 2020). If I sort by the value Sales using the "Sort by" on the visual header, it does not keep the country : 2018 2019 2020 structure that I want.
I tried to create a measure that computes the total sum per country :
The measure computes the total sales of all the Table and not only of the visual. It keeps the structure for each country 2018 2019 2020 which is good. However, the x-axis is not as I want, it is concatenated even though the concatenate label is off. This makes the x-axis unreadable because below each bar I have the country followed by the year. Is it possible to have the axis legend as the first graph that is with the year below each bar and the country written only once below the 3 bars?
Thank you for your help,
Regards
Hi Anonymous
You could add an index into the "Tooltip", then sort by [Index] on "Ascending order",
To create an index column, go to "Transform data",
group by "country" and get the total for each country,
then expand and rename columns,
next, sort by "Total" on Dscending firstly, sort by "Year" on Ascending secondly,
Finally, add an index column.
You could also format the color as below:Measure = SUM('Table'[Total])Best Regards
Maggie
Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
4 Replies
Create a new measure assume "value" is you measure
calculate([Value],allexcept(Table[country)]
Add this to chart and try to sort on that using three dots
Hi Anonymous
Do you want to sort by year and country, for example,
country a b
year 2018 2019 2020 2018 2019 2020
value 2 3 1 5 4 1
Or sort by value and country
country a b
year 2020 2018 2019 2020 2019 2018
value 1 2 3 1 4 5
Best Regards
Maggie
Hello v-juanli-msft,
What I would like is to sort by year and country but also by country with the highest total value.
Example:
Country B A CYear 2018 2019 2020 2018 2019 2020 2018 2019 2020
Value 1 5 3 4 1 1 1 2 1
(Total Value) (9) (6) (4)
Thank you,
Best RegardsHi Anonymous
You could add an index into the "Tooltip", then sort by [Index] on "Ascending order",
To create an index column, go to "Transform data",
group by "country" and get the total for each country,
then expand and rename columns,
next, sort by "Total" on Dscending firstly, sort by "Year" on Ascending secondly,
Finally, add an index column.
You could also format the color as below:Measure = SUM('Table'[Total])Best Regards
Maggie
Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
