Ex12#Create a Combination Chart in Power BI: Bar Chart with Line
PreviousEx11# Utilize the Focus Mode of a Chart in Power BINextEx13#Adding Static Visualization to Power BI Reports
Last updated
Last updated
In business intelligence, you are often required to visualize two measures that have different scales, such as income and age. In such cases, a combination chart is useful as it visualizes the multiple variables using a line and a bar chart with different axes. In this guide, you will learn how to create a bar chart with a line plot in Microsoft Power BI Desktop.
In this guide, you will work with a fictitious data set of bank loan disbursal across years. The data contains 3,000 observations and 17 variables. You can download the dataset. The major variables are described below:
Date: Loan disbursal date.
Income: Annual Income of the applicant (in US dollars).
Loan_disbursed: Loan amount (in US dollars) disbursed by the bank.
Age: The applicant’s age in years.
Gender: Whether the applicant is female (F) or male (M).
Interest_rate: Annual interest rate, in percentage, charged for the disbursed loan.
Start by loading the data.
Once you open Power BI Desktop, the following output is displayed.
Click on Get data option and select Excel from the options.
Browse to the location of the file and select it. The name of the file is BIdata.xlsx, and the sheet you will load is BIData sheet. The preview of the data is shown, and once you are satisfied that you are loading the right file, click Load.
You have loaded the file, and you can save the dashboard. It is named PowerBI Visualization. The Fields pane contains the variables in the data.
The Visualization pane located on the right side of the Power BI desktop contains the list of possible visualization charts. The chart you will use for creating the combination chart is Line and stacked column chart.
Click on the chart shown above, and it will create a chart box in the canvas. Nothing is displayed yet because you are yet to add the required visualization arguments. These are added in the options located under the Visualizations pane.
Visualize the Loan_disbursed variable and the average Interest_rate charged by the bank over the past several years. Start by dragging the Loan_disbursed variable into the Column values options and the Interest_rate variable into the Line values option. This selection will mean that the loan disbursement and the interest rate will be plotted as column and line charts, respectively.
The output above shows that the interest rate is not yet averaged. To correct this, click on the Interest_rate variable, and select Average.
Now you have the average interest rate displayed in the chart. You can stretch the chart to your desired size.
The output above shows a single aggregated value for the measures. That is because we have not specified the shared axis. Drag the Date variable into the Shared axis field. This is a date variable, so the entire hierarchy is generated.
The output above shows the combination chart with loan disbursed across years displayed as bars, while the average interest charged across years displayed in line.
The chart produced above contains default colors for both bar and line charts. Power BI provides several options to perform formatting. Click on the format option displayed in the small box below, and you will see different options. In the image below, you can see the option Data colors. You can use this option to change the color under Average of Interest_rate.
The chart above does not display data values. In some cases, you will want to add these into your chart. For this, you need to select the option Data labels as shown below.
There are other formatting options that you can explore. Another thing you can do is collapse the Filters option in the pane with the arrow > sign.
Completing the above step generates the following output. Now you have a bigger area for understanding your visualization chart.
Combination charts are used across industries because of their power to represent multivariate insights. Another advantage of combination charts, such as bar charts with lines, is that you can use them to visualize measures that belong to different scales, like revenue in dollars and units sold. You can use the bar chart with line plot in manufacturing, utilities, ecommerce, retail, and almost every industry.
In this guide, you learned how to build a bar chart with line combination chart in Power BI for fictitious bank data. This will help you perform multivariate analysis and visualization and strengthen your descriptive analytics skill.
