Sort values of calculated column in 100% stacked bar/column in ascending/descending order by value?

[Anonymous]

Dear Community members!

It always looks better, if biggest values are bottom and small and invisible ones in the top (or bottom).

As you may know, there is also a problem with legends, which show reverse order - if I change sorting in legends, colours in bars reverse also and result is the same.

Bad example (first picture):

I have a few years experience with SAP BO, where solution is very simple:
1)reversing legend order does not change order within stacked bar;
2) and most important - you can add a hidden dimension to value axis, by which to sort values, also with possibilities of custom order.
There are a lot of advices on internet and Power BI forum, which practically could be grouped as 3 solutions:
1) adding another column in related table by which to sort "problematic" column.
2) adding calculated column as a tooltip, so giving sorting possibilities, but it really does not work (does not sort within a bar);
3) using ribbon vizual, which I do not like, because it is not possible to change column width, even if it is possible to hide ribbons and most important, ribbon vizual is not 100% stacked.

Regarding 1) solution - it works fine if I create custom sort in related table by legend (category), i.e., knowing which legends(categories) are largest to smallest at the moment (second picture), but order can change over the time! This workaround is ok for categories where order does not change in time and amount of categories are few.

I tried to do a RANKX of calculated column in related table based on values/measures, but I get message "The expression refers to multiple columns. Multiple columns cannot be converted to scalar value" - of course RANKX requires its own logic.

Regarding legends order in sync with order in bar it seems that the only solution is just to put legends on top (it's like reading a book from left to right, also second picture) - on the right side it will always be reversed, but it would be the smallest issue now...

Any ideas to the main problem?
Many thanks!!!

@Fowmy 

[amitchandak]

@Anonymous , for legend sort, we have option in the latest release, that is ascending or descending

Then we can use the sort column. All the options are  static workarounds.  As sort column is column you can not have measure/ value-based dynamic sorting

[Anonymous]

hello, you mean in latest release sorting ascending/descending of legend alphabetically?

[amitchandak]

@Anonymous , option is there on new versions, under three dots

[Anonymous]

amitchandak, I know very well 3 dots option, it sorts legend alphabetically, this is where problem lies. Moreover, sorting by fact (value) in some way contradicts other sorting and disrupts sorting of period 🙂

PREVIEW

[AlexisOlson]

You are not limited to alphabetical sorting since columns can be sorted by other columns.

However, this still doesn't allow for dynamic sorting based on slicers since calculated columns cannot be responsive to slicers.