Sort stacked bar chart - add secondary criteria in case total values are the same

[itskool]

Hi All,

I've run into a weird problem: when I create stacked bar chart and sort it by total value, when then total values are the same, the visual gives preference to the bar where there are more yellow values than red. The business requirement is to make it vice versa.
I've added a sort column to the column which is in the legend bucket, tried it both ways (red 1 yellow 2, red 2 yellow 1), it is still the same. 

Funnily enough, if I sort it ascending , it works as I expect it (5 is below 3), so it makes me think that this order is random.
Any ideas? Is there a way to add this secondary sorting criteria?

[ribisht17]

"➨Please mark the relevant answer it it helped you so that it can help others too

  ∎  Help the community back!

[dk_dk · Accepted solution]

Hi @itskool 

I have a solution, it is a bit wonky but seems to work nonetheless.

Assuming you have data that looks something like this:

And you create a chart based on this table, where Item is the Y-axis, Sum of Value is the X-axis, and Color is the Legend.

1.  Create a helper table (called Sorting) that contains only the unique  Items from your main table. Many ways to do this, I did it in Power Query, by referencing the main table, removing all other columns except Item and removing duplicate rows.
2. add a calculated column to the Sorting table:
   

   
   SortOrderHelper =
   VAR curritem = Sorting[Item]
   VAR totVal = CALCULATE(SUM('Table'[Value]),'Table'[Item]=curritem)
   VAR redVal = CALCULATE(SUM('Table'[Value]),'Table'[Item]=curritem, 'Table'[Color]="Red")

   RETURN
   totVal+(redVal/100)​

   
   This will calculate a custom sort order, primarily based on the total value, but in case of tiebreaks it will give a higher rank to the item with more red.
3. Create a calculated column in your original table that looks up this value:
   
   SortOrder = LOOKUPVALUE(Sorting[SortOrderHelper],Sorting[Item],'Table'[Item])​
   
   
4. Add the SortOrder column to your visual, into the tooltips section. Set aggregation to Max (though Min, Sum, or Avg should also work)

5. Sort axis by Max of SortOrder, and it should get you the correct sorting with red being prioritised for tiebreaks.

   

Note: Depending on your data, the division by 100 might not be sufficient  (if you have 100+ red values for an item, it might mess up the order completely). You can change it to a higher number, or create a VAR that calculates the largest red value dynamically, add +1 to that and divide by that amount (this is so you never have a redVal fraction that is > 1).
Also make sure that the calculated columns you create are formatted as decimal numbers, with enough decimals to show the difference in the tiebreaks.


I hope this helps, let me know if you have any questions. 

[ribisht17]

hi @itskool 

The game here is to reverse the alphabetical order of the Legend 

"f" comes first, hence it is on the LHS

I wrote a calculation and bring the same to legend and I was able to reverse it

Regards,

Ritesh

"➨Please mark the answer it it helped you so that it can help others too

  ∎  My You tube Channel Dance-Sing with Data Projects - YouTube"