How do you sort stacked bar chart by value

Question & Answer
, ,
1

The stacked groups in a Vertical stacked bar chart can be sorted alphabetically. However, I want to sort them by value where the largest value shows up in the bottom. Is there any way to do this?

1 Like
2

Hi @sambhavbhandari -

Currently QS will not sort the Group/Color using a value. Here is a workaround that you can use.

Step 1 - Create a calculated field that will show your stacked group plus what rank the value is.

c_rank:

rank([value DESC],[col2], PRE_AGG)

Step 2 - Concat your rank + your stacked group.

c_sort_col1_string:

concat(tostring({c_rank}),col1)

Step 3 - Change the legend colors so that all stacked groups are the same color.

Step 4 - Hide the legend and create your own legend with a custom visual (if desired)

Result:

image
image938×719 36.4 KB

2 Likes
3

Hi. How can you make this work when you want to sort not by a value but through an aggregated value (ex: sum({value}) DESC in your example). The problem I run into then is that I cannot create the c_sort_col1_string since it cannot mix aggregated and non-aggregated fields in the concat.

Thanks in advance for your help.

The possibility to sort by something else than the alphabetical order of the group would be more than welcome…

4

Hi @cedneve -

You can use a level aware aggregation in your calculate field.

concat(toString(sumOver(value, [], PRE_AGG)),'col1')
5

Hi there!
Is there still no solution from QuickSight to solve that ? I am facing the same issue and none of the trick mentioned is working, as I also would need to concat aggregate and non-aggregated fields.
Thanks for your help!

6

Hi @Elope - We’ve captured this feature request but you currently you can’t sort by group by/color in stacked bar charts.

The workaround above is still the only workaround I can think of. Using a LAC-W function like sumOver will allow you to concat aggregated and non-aggregated fields.

1 Like
7

Hi @robdhondt,

After applying the proposed solution, I have different colors for the same value. i.e: using your example, 1zap is red in one bar and blue in another. It seems that it is because in one bar it is 1zap and in another is it 3zap because of the rank value.

Is there a way to make zap have the same color in all bars? I’m using zap as an example, but I would like to have every value to have the same color across all bars