Although very simple, still I get this question very often; How can I show the total value as a data label in a stacked column chart? In this quick blog post, I’m going to show you a quick and easy method to use a combo chart and show the total as a label on top of the stacked column chart. If you like to learn more about Power BI, read Power BI book from Rookie to Rock Star.
Let’s say you want to see the SalesAmount (from FactInternetSales table) sliced and diced by EnglishEducation (from the DimCustomer table), and using Gender (from the DimCustomer table) as the legend in a stacked column chart. this is an example of what it looks like;
Now, let’s say we want to add the data labels in the chart, which we can do it in the Format tab, with turning on the data label.
As you can see, there are data labels for each subcategory (means gender and education), but no data label showing the total of each education category. for example, we want to know how much was the total sales in the High School category. Now that you know the problem, let’s see a way to fix it.
Combo Chart: Line and Stacked Column Chart
One easy solution for the problem above is to use a combo chart. In Power BI world we call these charts line and column charts. In this example, we need a Line and Stacked Column Chart.
After changing the chart type to this visual, you can see that there is a Line Value property. Set it to SalesAmount. (the same field which we have in Column Values)
And don’t worry about slicing and dicing by Gender, because Gender is in Column Series, and won’t affect the Line Values at all.
Now you can see a line in the chart, showing totals of each education category;
Showing values in a line chart only make sense when the axis is a sequence. In our case, it is not, so we are going to hide the line by changing the stroke width to zero. Go to the Format tab, and under Shapes, set the Stroke width to zero.
You can also do more customization, such as showing the marker, setting the shape and size and color for the marker as below;
Here is the output of actions above;
This was a quick post to show how you can add total values to a stacked column chart. In this post, I’ve used Line and Stacked Chart and changed the stroke width of the line to zero to hide it. There are other benefits of using Line and Stacked Column Chart as well, such as showing values which are not on the same scale, I will write about that later, stay tuned!