Forum Discussion
Sort a stacked column chart with the largest bar down (first)
Hello everyone!
As you can see in the attached image, I have this stacked column chart, what I would like to do is sort first as the largest, so the red with 285 would be the base, followed by the blue with 277 ...
It's possible? I've searched some forums, but they talk about creating a measure for each value, but I have almost 11 values.
Best regards
1 Reply
- DataZoe
Microsoft Employee
Anonymous ,
Ok I did find a way, though to be honest it's not very elegant.
The ribbon chart can sort each stack if there is more than one stack, so
- I created a calculated column in the main table that simply [Column1] = 1, then a table with 1's in the first column (to link to the 1 column) and then 1, 2 in the second column:
- I created a relationship on the 1s. ('Ribbon'[Value1] to 'Table'[Column1])
- Then I used Value2 on the axis which will then show 2 sorted bars with the same values.
- It sorts by desc, so I created a measure that multipled the desired measure by -1 to have it sort asc.
You now have a couple options for formatting it:
- Make ribbons transparency = 0 and turn off tooltips. (so you don't get the rank tooltip on the ribbons and the negative numbers showing)
- Make ribbons fully transparent = 100, then hide the second stack with a well placed shape. Make sure maintain layer order is on both the chart and shape. Create a tooltip page to replace the default tooltip for the bars to show the number correctly.
I liked option 2 best because there were faint lines visible on option 1 that I couldn't figure out how to get rid of.
You can also create stuff like spacing between the sections which is kind of nice.
I've attached the PBIX file.
