Ex3# Building Line Charts in Power BI
Introduction
Background: In recent times, there has been an increasing focus on the impact of gender on financial terms offered by institutions. Your financial consultancy firm has been at the forefront of analyzing such socio-economic factors and their influence on financial products. One area of particular interest is the difference in interest rates offered to individuals based on gender over a span of several years.
The Task: A client, a non-profit organization working towards gender equality in financial access, has requested a detailed analysis of interest rates for loans to understand if there is a gender bias that needs to be addressed.
Your Role: As a lead data analyst, your role is to dissect the historical loan data and interpret the fluctuations in average interest rates charged to different genders over the years. You are expected to present your findings in a clear, visual format that the client can use in presentations to stakeholders.
Data Overview: The dataset includes loan records from the year 2011 to 2019 with fields such as the date of loan issuance, the interest rate charged, and the gender of the loan recipient.
Specific Challenges:
Craft a narrative that explains the trends and anomalies in the average interest rates for different genders across the years.
Use the data to determine if there are any noticeable patterns that suggest gender-based disparities.
Address the challenge of effectively communicating complex data trends to a non-technical audience.
Expected Outcome: A comprehensive report that includes a line chart. The report should detail the year-by-year comparison of average interest rates for different genders, highlighting any points of convergence or divergence. Additionally, the report should include an executive summary with insights and potential recommendations for the client's advocacy work.
A line chart is a popular visualization chart used to represent a series of data points connected by a straight line. It is used to represent continuous data sets. Time series forecasting and stock market trend analysis are common applications of line charts. This guide will demonstrate how to build line charts in Power BI Desktop.
Data
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 here. 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.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.
Loading 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 of the data.
Adding Line Chart
The first step is to add a Line chart from the Visualizations pane.
Drag the variable Date into the Axis field, and Interest_rate in the Values field.
The above line chart is incorrect as it adds the interest rate values. To make the correction, right click and select Average.
The above step will produce the correct line chart that displays the average interest rate.
Format Line Charts
The Format field in Power BI Desktop provides several options to format the chart. To begin, turn on the Data labels as shown below.
You can see the data labels plotted in the line chart, but the text size is small. Set the Text size to twelve.
You can also add new dimensions to the chart. For example, drag the Gender variable into the Legend option. This creates two line charts corresponding to gender.
If you want to change the color of either of the line chart, that is also possible. Go to Data colors and you will see the default color for both gender.
Click on the default color for F and several color options will be displayed.
Pick the color of your choice or as shown in the chart below to generate the following output.
You can also format the title of the chart. Go to the Title option under Format.
Change the Font color, Alignment, and Text size of the title. This will generate the resulting output.
Conclusion
Line charts are used across industries because of their power to represent changes across time. They are used for time series forecasting and in various financial reporting tasks. Another advantage is that you can represent changes in multiple variables across time. Line charts are used across sectors and knowledge of building line charts is a useful skill set to enhance your business intelligence capabilities.
Last updated
