In this Microsoft Power Bi tutorial, we will learn about Power BI stacked column chart. Also, we will discuss:
- What is a Power BI Stacked Column chart?
- When to use this Stacked Column chart?
- Discuss the main parts of the Stacked Column chart
- How to create a stacked column chart using excel?
- How to create a stacked column chart using a SharePoint list?
- Power BI Stacked Column chart formatting
- Power BI Stacked Column chart multiple values
- Power BI Stacked Column chart show percentage
- Stacked Column chart show/hide Totals
- Power BI Stacked Column chart sorting
- Advantages and disadvantages of using a Stacked Column chart
Power BI Stacked Column chart
A Power BI Stacked Column chart based on column bars, which comprise one or multiple legends. In a Stacked Column chart, data series are stacked one on top of the other in vertical columns. Stacked column charts show changes over time and also it is easy to compare total column length.
In a Stacked Column chart, the Axis is represented on X-axis, and Data is represented on Y-axis. Let’s have a look at the below example:
In the above example, we can see the Axis(Product) represented on X-axis, whether Y-axis representing the data/values(Sales).
When to use Stacked Column chart in Power Bi
Now, we will discuss in which cases we can use this Stacked Column chart to visualize the data. The cases are given below:
- The Stacked Column charts are great to represent the parts of multiple totals.
- This chart work well for only a few totals.
- The Stacked Column charts can work great for showing data over Date.
- We can use this chart for representing time data, having same intervals.
- This chart works better if the total have short labels.
Stacked Column chart components
Before creating a Power BI Stacked Column chart, first, we have to consider these main parts which are described below:
- Title: This described the short information about the visual.
- X-Axis(Horizontal): It defines the individual items presented on the data.
- Y-Axis(Vertical): It defines the value from lowest to highest intervals.
- Bar: It represents the total value as the sum of the total value of all legends.
- Legends: It shows the types or categories of the dataset, that contribute to the column bars.
How to create a Stacked Column chart on Power BI?
Now, we will see how to create a Stacked Column chart on Power BI from Excel and SharePoint Online list. Consider the below Examples.
How to create a Stacked Column chart using Excel
By following these steps, we can easily create a Stacked Column chart using Excel.
For creating a Stacked column chart we have to prepare excel data or we can download it from the browser. Here we are using sample data, which we downloaded from the browser.
Open Power BI Desktop > Get data > More… > All/File > Excel > Connect.
After connecting, select the downloaded excel data > Open.
Now, on navigator page navigate the Selected excel data > click on Transfer data.
When you click on Transfer data, data will open on Power query editor. Here we can format the data type, add new column, remove unnecessary columns, rename the column, etc. For example, we format the unit sold data type from decimal number to whole number.
After formatting, click on Close & apply.
On report page, select Stacked column chart from visualizations. For example, we will create a visual using stacked column chart which shows the total sales by Product. Here we taken these fields(by drag-drop):
- Axis: Product
- Values: Sales
- Legend: Product
On this above chart, it shows the product in horizontal line, also as legend. Sales define as product’s sale having lowest to highest value having a particular intervals.
There is another option, Small multiples which splits the visual into multiple parts of itself. Let’s have a look on below visual:
We can see in above visual, after applying month name as small multiples the visual got split into multiple parts of itself. This is how to create create a Stacked Column chart from Excel.
Read: Power Bi key influencers
By following these below steps we will learn how to create a stacked column chart using the SharePoint list.
First, we have to prepare a SharePoint list. For example, we create a SharePoint list on Products like below:
Open Power BI Desktop > Get data > More… > Online Services > SharePoint Online List > Connect.
A dialogue page will appear, enter here your SharePoint site URL > Ok.
After clicking OK, navigate your selected SharePoint List to Power BI. Select Transform Data.
On Power Query editor remove all the unnecessary columns except SharePoint List’s columns. Then click on Close & Apply.
Now, on the report page select a stacked column chart from visualizations. For example, we will create a visual which shows Total product quantity by City. Here we taken these fields(by drag-drop):
- Axis: City
- Legends: City
- Values: Quantity
This is how to create a Stacked column chart using SharePoint List. Like this We can create stacked column chart using CSV files, SQL Database, XML, Oracle database… etc.
Power BI Stacked Column chart formatting
Some basic formats are available on Power BI by which we can develop the visual. Lets discuss about some important formats here. Select the visual > Click on Format Pane.
- Legend: By using On/Off toggle we can show or hide the legend on the visual. Also, we can format the legend’s Position(top to right-center), legend’s title(city to cities), it’s color(black to red), etc.
- X-axis: By using On/Off toggle we can show or hide the items of X-axis on the visual. Also, it allows to format it’s color(black to green), text size(9 to 10pt) and many more.
- Y-axis: Here we can format its position, Display units, text size and its colors etc.
- Data colors: Here we can customize the colors of each data(Columbus’s color Orange to Yellow).
- Data label: By turning on the data label, we can see the data units are displaying inside the bars. We can format the display units and positions, text size, etc.
- Total label: Here we can show/hide the total of the Products.
- Title: Here we can format the title of the Visual(Quantity by city and city to Total Product quantity by city), font color, alignment(left to center), font size, etc. And so on.
We can see the differences between both visuals. Now, the visual will be look like this:
This is how todo format on Power BI Stacked Column Chart.
Power BI Stacked Column chart multiple values
In Power BI Stacked Column Chart, we can show multiple data by adding multiple values. For implementing this we have to follow these steps:
On the Report page, add a Stacked Column chart from the visualization. Now we will create a visual, that will represent the Product data with multiple values, i,e, Product’s profit, Product sales, COGS, etc.
In Axis we will add Product and in value field, we will add multiple values like below:
Now, the chart will look like this:
We can see, that each product(on X-axis) having multiple values. When mousing over on the particular segment it will show the details of the product.
Power bi stacked column chart show percentage
Conditional formatting can be applied to the column charts by specifying a measure, such as product /profit margin in the color saturation field. Now, we will see how to show percentage on Power BI stacked Column chart by following these easy steps:
Create a stacked column chart. For example, here the chart visualize the data as sales by product and country. We have taken the field as:
- Axis: Product
- Legend: Country
- Values: Sales
Make sure to turn ON the data label on the format pane. Then the visual will be look like below:
Select the drop-down of Sales(on Value field) > Show value as > select Percent of Grand total.
Then the visual will be present like below:
Power bi stacked column chart conditional formatting
The Power BI Stacked Column chart allows doing conditional formatting on Data colors. By these following steps, we can do conditional formatting on Stacked column chart:
Create a stacked column chart. For example, we will create a stacked column chart that visualizes the data as Profit by Country. Here we have taken fields as:
- Axis: Country
- Values: Profit
Then, the chart will look like this:
Go to Format pane > Data color > click on Conditional formatting(fx)
Then it will redirect a, where we can apply conditions to formatting the visual. There are 3 types of formats as Color scale, Rules, Field value. Let’s do format by Color scale.
We can set the lowest value or custom value and its respective color from the color chart in this format. Like Minimum, we can apply conditions on Center and Maximum. Then click on OK.
We can see the condition is being applied on Stacked column chart.
This is how to do conditional formatting on Power BI Stacked Column Chart.
Power bi stacked column chart total
For displaying the Totals in a stacked column chart, we have to follow these easy steps:
Create a Stacked Column chart having fields Axis, Legend, Values. Here, we will create a stacked column chart having fields as:
- Axis: Product
- Legend: Country
- Values: Sales
Go to Format pane > turn On Total label. We can see the total is showing on the Stacked column chart. We can format its display units, color, font family, size, etc.
We can see the Total and Sales when we mouse hover on a particular segment.
Read: Power Bi Pyramid chart
Power bi stacked column chart sort
We can sort our data directly in Stacked Column chart. Lets see example of Power bi stacked column chart sort:
Example-1: How to do sorting on Power BI Stacked Column chart:
Create a Stacked column chart. Here we prepare a chart displaying the data as Manufacturing price by Product, having fields:
- Axis: Product
- Values: Sale Price
- Legend: Product
As the visual by-default arranged in a descending order(by Price), so we will do sorting it by Product
Click on More Option > Sort by > Product.
We can see the visual is sorting by-products with descending order(alphabetically). There is a yellow trigger(left side on Product, sort descending)that indicating our option is being selected.
Example-2: How to do Legend sorting on Power BI
Here we have taken the Product as Legend. Let’s do sort by ascending order on legends. For this, here we continuing from the previous step:
On that(previous)visual, Click on More Option > Sort by > Product > Sort ascending
We can see that the Legends are sorted in ascending order(alphabetically). This is how we can do sort the legend on the Power BI Stacked Column chart.
You may like to read:
Advantages and disadvantages of Stacked Column chart
Advantages of Stacked Column chart:
- Easily created and understandable.
- Displaying data individually from the whole dataset.
- It can show changes over time.
- It is useful where the entities to be measured are discrete such as Marks.
Disadvantages of Stacked Column chart:
- As categories and data series are added, stacked column charts quickly become complicated.
- It is not a good choice if your dataset is too large.
You may like the following Power BI tutorials:
- Display COVID-19 Power bi report in PowerApps
- Power Bi Bar Chart
- Power BI Pie Chart – Complete Tutorial
- Power Bi line chart (Helpful tutorial)
- Power bi desktop vs Power bi service vs Power bi premium
From this above tutorial, we learned all about Stacked Column chart, also we learned about:
- What is the Power BI Stacked Column chart and when to use this?
- Discuss the main parts of the Stacked Column chart?
- How to create a stacked column chart using various datasets?
- Basic formatting on Power BI Stacked Column chart?
- How to show the percentage and do sorting on Power BI Stacked Column chart?
- Displaying Totals in a Stacked Column chart?
- Discussed advantages and disadvantages of using this chart?
I am Bijay from Odisha, India. Currently working in my own venture TSInfo Technologies in Bangalore, India. I am Microsoft Office Servers and Services (SharePoint) MVP (5 times). I works in SharePoint 2016/2013/2010, SharePoint Online Office 365 etc. Check out My MVP Profile.. I also run popular SharePoint web site SPGuides.com