Showing the Total Value in Stacked Column Chart in Power BI

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.

Prerequisite

The dataset for this model is the AdventureWorksDW2012 Excel file, which you can download from here. Tables used in this example are; DimCustomer and FactInternetSales.

Problem

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;

Customize Formatting

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;

Summary

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!

Video

Reza Rad on FacebookReza Rad on LinkedinReza Rad on TwitterReza Rad on Youtube
Reza Rad
Trainer, Consultant, Mentor
Reza Rad is a Microsoft Regional Director, an Author, Trainer, Speaker and Consultant. He has a BSc in Computer engineering; he has more than 20 years’ experience in data analysis, BI, databases, programming, and development mostly on Microsoft technologies. He is a Microsoft Data Platform MVP for 12 continuous years (from 2011 till now) for his dedication in Microsoft BI. Reza is an active blogger and co-founder of RADACAD. Reza is also co-founder and co-organizer of Difinity conference in New Zealand, Power BI Summit, and Data Insight Summit.
Reza is author of more than 14 books on Microsoft Business Intelligence, most of these books are published under Power BI category. Among these are books such as Power BI DAX Simplified, Pro Power BI Architecture, Power BI from Rookie to Rock Star, Power Query books series, Row-Level Security in Power BI and etc.
He is an International Speaker in Microsoft Ignite, Microsoft Business Applications Summit, Data Insight Summit, PASS Summit, SQL Saturday and SQL user groups. And He is a Microsoft Certified Trainer.
Reza’s passion is to help you find the best data solution, he is Data enthusiast.
His articles on different aspects of technologies, especially on MS BI, can be found on his blog: https://radacad.com/blog.

21 thoughts on “Showing the Total Value in Stacked Column Chart in Power BI

  • This is useful (and should work in all situations), but is also the kind of hackaround needed because Power BI hasn’t implemented a user requested feature.

    • Hi Cody.
      Correct. However, I’m sure Power BI will have this and many other small features like that implemented soon 🙂
      Cheers
      Reza

    • unfortunately not possible by default, unless you combine it with another DAX trick.
      but what would be the point of such visualization? you already have to combined stacked column anyway!
      Cheers
      Reza

  • I have used this technique many times. One issue though is that you can’t just show the totals because you can’t independently control the formatting of SalesAmount as both a column and line. You will only see it once in the formatting pane. The only workaround I could think of was to create another measure SalesAmount2 to use for the line. Then you can turn the display of data labels off for SalesAmount and on for SalesAmount2.

  • This is great, but in my case I the values are counts and I can’t get it to work. The line points are not at the top of each column – sometimes they are in the middle. It doesn’t makes sense to me since both the columns and line are using the same field and metric (count – not distinct). Any idea why I may be having that issue?

  • I use this technique, but instead of setting the line stroke width to zero, I set it to 1 or 2, color the bar a distinct color that stands out (usually black) and change the line to Stepped. This essentially out lines the Total and makes the value appear distinct to the segments of the bar. It really looks nice when the chart has a lot of bars but works well even down to 4-5.

  • This is all good, but I think it won’t work when the line value is a percentage and your stacked bars are numbers. Is that right? Thanks!

    • You can have multiple line values. but if you have one as a percentage and the other one as value, then you will get into scaling issues, that the same axis won’t be good for them both.
      Cheers
      Reza

  • It’s a good solution, but the theory about datavisualization wouls say:
    – use column’s for time axis
    – use bar’s for categories

    How would you achieve this with bar’s? PowerBI does not seem to have the correct visual?

    • Hi Wouter
      I do not understand your question! We have both bar chart and column chart in Power BI, and you can use them for time or categories. Can you explain more what you are trying to achieve?
      Cheers
      Reza

  • Hi Reza! thank you for you manual. I have a question – is it possible to up the total value for bachelor column? you can see that all other total values are above columns but the bachelor total value inside column. I know that it is because of end of Y-axis and if we change the end value manually it would be correct but for example if i don’t know what the total value i will have in this month it won’t be possible to do it manually.
    Could you please help me with this issue?

    thank you,

    • There are two ways you can do that;
      1. change the Y-Axis for both Column and Line, and set their End value is higher value so that it can have space to show that number above the column.
      2. Under Data Labels, set the Customize Series On, and then select Sales Amount in the dropdown, and set the position to be Above.

Leave a Reply