I have a stacked bar with below data. The year comes in the X axis and sales stacked on the Y based on region, and region will be displayed on the legend. I would like to sort the legend based on total sales, but B will always be at the top since the highest amount in 2018 is with B. The graph is sorted first based on Year ascending. I tried using formula sorting for sales , but still B comes at the top.
year | region | Sales |
2018 | A | 40 |
2018 | B | 60 |
2019 | A | 20 |
2020 | A | 100 |
2020 | C | 110 |