It’s been a while since the last JTT, so I thought I’d get back in the groove of things by showing a quick and easy way to show total labels as well as composite labels on your stacked bar charts. No LODS or dual axes needed!
Starting Point
Using our faithful friend Superstore (in 2018.1), I’ve put Region on columns, Sum(sales) on rows and label, and Category on Colour. We can already read off the value of the top of each stacked bar from the axis, but it would be nice to have the total sales per region easily visible too. As I mentioned earlier, we could do this with an LOD:
{EXCLUDE [Category]: SUM([Sales])
and put that on label, or faff about with dual axes, but that seems like more trouble than it’s worth. What we really want is something like this:
Reference Lines are your friend
I use reference lines a lot, mostly for sorting out formatting. They are super quick and easy to use, and are exactly what we want here. You can add them by going to the Analytics Pane, and dragging them onto your view, but this time I’ve opted for right clicking the axis and adding one from there.
After you do this, you’re greeted by the reference line dialogue box.
We want to add a reference line at the top of each bar, showing the total sales for that bar. We want to label this line, and then get rid of the line itself, just leaving the label. So we need a reference line on each cell, showing Sum(Sales), labelled by the value and with no line. Set up your box like this:
This gives us a view with labels on the top left of each bar (like hats at jaunty angles), but now all we need to do is format the labels – increase size and middle align – to get our final result.
That’s it for now. If you enjoyed this blog, found it useful, or want to get in touch about anything, then you can always find me on twitter @olliehclarke.