Vertical stacked 100% bar chart - Apply color and group based on percent calculations

Hello,
I am trying to group some colors based on a percentage but it does not seem to work.
Original chart is below

I want to group all cities with more than 8 % in sales into a ‘good’ color and for the rest I just want to show their respective percentages to reduce the number of colors displayed.
I have used the calculated field called “Group city Color” below:
ifelse(Sales > 8/100, ‘good’, {Sales}) but it gives an error

I also tried another formula and placed it into the group/color section but it only shows one color

I appreciate any help I can get.
Thanks.

So you want to say that any city with more than 8% of the total sales for that quarter is good? Else return the city?

You might look to do something like this.

ifelse(sumOver({sales},[{Order Date Quarter},{city}],PRE_AGG)/sumOver({sales},[{Order Date Quarter}],PRE_AGG)>.08,‘Good’,{city})

You would then put this into your group/color. Let me know if that works ^

To explain a little more of why your previous attempts were failing:
ifelse(Sales > 8/100, ‘good’, {Sales}) failed because it returned two different data types. One was a string ‘Good’, the other was a number Sales.

ifelse(Sales > 8/100, ‘good’, ‘bad’) failed because this is just checking that at the particular row, is the sales greater than .08? If it is then it’s good. So this is saying that all of your sales were greater than .08.

1 Like

Thank you, Max. It worked beautifully!

1 Like

Thanks for letting us know and marking this as a solution @Serginaud! And thanks, @Max, for sharing this solution!