Showing the Total Value in Stacked Column Chart in Power BI

Facebooktwittergoogle_plusredditpinterestlinkedintumblrmailFacebooktwittergoogle_plusredditpinterestlinkedintumblrmail

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!

Facebooktwittergoogle_plusredditpinterestlinkedintumblrmailFacebooktwittergoogle_plusredditpinterestlinkedintumblrmail
rssyoutuberssyoutube
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 eight 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.
His articles on different aspects of technologies, especially on MS BI, can be found on his blog: https://radacad.com/blog.
He wrote some books on MS SQL BI and also is writing some others, He was also an active member on online technical forums such as MSDN and Experts-Exchange, and was a moderator of MSDN SQL Server forums, and is an MCP, MCSE, and MCITP of BI. He is the leader of the New Zealand Business Intelligence users group. He is also the author of very popular book Power BI from Rookie to Rock Star, which is free with more than 1700 pages of content and the Power BI Pro Architecture published by Apress.
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.

11 Comments

  • 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.

    • 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.

Leave a Reply

Your email address will not be published. Required fields are marked *