Total Value in a Stacked Column Chart
I have recently run into the frustration that I cannot display a data label for the Total Value for a stacked column chart in Power BI. Data labels are only displayed for each category of my stacked chart, however, no data label is displayed for the Total Value of a stacked column.
Quite frustrating right? I did some research and spoke to some colleagues and saw that this is quite a common issue. After searching the web and playing around a bit I got to a workable solution.
Lets first talk through the problem.
We want to see the number of cars sold per month by gender in a stacked column, but also we want to see in our stacked column chart the Total Sales per month. The stacked column chart in Power BI will display the following chart.
As seen in the above image is the fact that we only have data labels for each category, male and female, but not a data label for the total sales per month. Now that we have a visual representation of the problem let’s see how we can go about rectifying this to get to a workable solution.
The solution: A-Line and Stacked column chart
A quick and easy solution to our problem is to use a combo chart. We will make use of Power BI’s Line and Stacked Column Chart.
When changing your stacked column chart to a Line and Stacked Column Chart the Line Value field appears. In our example, we will add the Total Sales to the Line Value field.
Your chart will now look like the following image with a line included, displaying the total value.
In our case we do not want to have a line in our chart, although it would make total sense to display the trend for total sales by month for the year, we would rather just want to show the total for each column.
Firstly, to get rid of the line we need to go to the Format tab, and you will see under Shapes a field called Stroke width that needs to be set to zero.
This is great in the below image you can see now that the line has been removed and what is left is exactly what we wanted to solve. A stacked column chart with the total values displayed by category and by the total.
We can enhance this chart even further by adding a Marker to the Totals so that it is a bit more logical when looking at the chart that the specific value is the total value.
We will have the final output of our chart as below:
This is all for this short post, enjoy solving your companies data problems with Power BI and always look for innovating ways to overcome some limitations in Power BI as explained in this post.
More great posts by die DataBear team here.
