Sorting Stacked Bar Chart by Totals and Displaying Bar Total

This is annoying the crud out of me.

How do I sort a stacked bar chart by the total of the bar? Not by one of the segments, and not by manually picking the order.

Also, how do I display the bar total as a label. Again, not for each of the segments.

Any help is appreciated and creating a forum post so others can find the answer.

The way that I usually do it is to add another metric set to the chart.
This new metric set will be the same as the 1st one, but I create a new formula measure that sums all the segments within each bar. Hide all the other measures in this metric set.
Then I set this new measure in the chart->series properties to be ‘point’ chart type, and set the height of the point to 0. Then set the label to display ‘right’. (or top if it’s vertical) This means that the label is displayed at the end of the bar, as if it belongs there.
Then use this to sort (you may need to play with the order of the displayed series).

That’s how I do it, don’t know if there’s another way

Thanks David. Seems like a lot to just label and sort a stacked bar. @jeff, any suggestions?

I’ll also reach out to support to see if they have a method. Crazy to have to add a whole other metric set.

Hi @christopher.simpson - i would have done something similar to @david.glickman . Create a formula that does a SUM and then make the bars transparent with labels that still show. Happy to create a sample if you’d like one, just let me know!

Come to think of it, I don’t remember why I had to do a separate metric - I think it was to do with having numbers in for the percentages but that weren’t being displayed.

On second thoughts, as @jeff says, a formula in the same metric set should do it.

Thanks David and Jeff. After several emails, I have a solution that does not require extra formulas or metric sets, just a few odd configurations. Also, due to the data set type, there isn’t really any quantitative measures to set Sum formulas on, as it is text records.

The data set is a list of quality issues for the company. I wanted to create a stacked bar that showed the number of issues by type with colors showing the different divisions.

Essentially, this:

Started with a simple bar chart

Then go to Visualization, and add Division to Color. Revisualize as a Stacked Bar, and Delete Division from Horizontal Axis (which is auto added when you add it to Color).

Now I can sort on Count Measure as expected.

The last bit is the Total label. I have to go to the Properties, Count Measure, Text, and add a Data Point Label.

For Text, there is an auto-generated [Total] field now. This adds a cumulative total running up the bar. Which is a step in the right direction, but I just want to have the totals.

In order to do this, I needed to configure some of the Layout setting for the data label. Change Placement to Outside, Outside Alignment to Top, Max Move Distance to 0, and ensure “Can Overlap Data” is not checked. This essentially forced the subtotals to be hidden by the data.

Hopefully this helps some other folks in the future.

7 Likes

Hi I did not understand the last solution,its not giving the total correctly if it was the requirement if the orange+blue bar values should be summed up than its not happening. I have the same requirements to show the totals at the top or in a tooltop for all the values how can i do that?

Hi Mitsu,

I’ve used this method many times since getting it worked out, you might be missing a step. What are you experiencing?

Hi Christopher,

so let us take the chart from your solution ,you have second bar with values 88 as blue and 79 as orange , i want to show those 2 values inside the bar as well as their totals 167 at the top.how can achieve the same?

So the second bar is an orange bar of 79 and a blue bar of 9. It’s a normal axis, not logarithmic. The picture above that is showing a cumulative subtotal, which is why it says 79 and then 88.

In order to have it show the series data points in the bars and the total at the top, I think you will need to create a formula in the data panel and plot it as an invisible line chart with data labels.

Hi,

Yes when using the [Total] keyword, this is a cumulative total as Christopher said.

You can display more than one set of data point labels per series: one set of Data Label Settings can be set up as shown here with a total label outside on top, and another with Placement set to Inside that displays the actual measure value in the Text property instead of [Total]. This is described here: https://www.dundas.com/support/learning/documentation/data-visualizations/using-chart-properties#h6-6-data-point-labels

Good to know Jamie, thanks.

Thanks Christopher and Jamie for the help. :slight_smile:

1 Like