Rank stacked column chart

[Anonymous]

Hi, 

Currently , I have stacked column chart below. 

The order of legend is defaultly as A-Z. 

How to rank the legends based on the YTD for every legend? 

Thanks for any help 🙂

[AllisonKennedy]

You can change the order of the bars to sort by YTD value, but not the legend values. You can use Sort By Column to re-order the legend, but this may be difficult to do based on YTD value as there are some restrictions around how that can work.

[Anonymous]

@AllisonKennedy , @amitchandak 

Thanks for your quick help. 

I may want to change the chart as below excel format.  To sort by the amount for every category based on YTD , or it's OK to sort the amount for every category in each month. 

Thanks!

[amitchandak]

@Anonymous , I think legend sorting happens alphabetically. Check if there any idea https://ideas.powerbi.com/ideas/ 

[Anonymous]

@amitchandak ,

Thanks for your reply. 

I may ask in another way, I know the legend sorting is not possible. 

What if I rank them in a table? and then sort them as the rank column? is it possible?

[AllisonKennedy · Accepted solution]

You could use RANKX to create a calculated column that ranks each category. You just have to make sure that each category only has 1 rank, so every year for that category and every month it must be the same number ranking. 



Then you can use Sort By Columns to sort the category by rank and the legend will automatically update.

https://docs.microsoft.com/en-us/power-bi/create-reports/desktop-sort-by-column


You may also be interested in Ribbon chart, which shows relative rank for each category and how it has changed from month to month: https://docs.microsoft.com/en-us/power-bi/visuals/desktop-ribbon-charts 

[amitchandak]

@Anonymous , I doubt you can do that for legends. Please check and vote for an idea.

https://ideas.powerbi.com/ideas/