Ex2# Explore the Analytics Pane in Power BI
Introduction
Background: You are a data analyst at a prominent financial consultancy firm, known for its meticulous analysis and insightful reports on market trends and financial data. The firm has been approached by a consortium of financial institutions seeking to understand the dynamics of loan disbursements over time to better tailor their loan products and services.
The Task: Your team has collected extensive data on loans disbursed across various months and now requires a comprehensive visual analysis to identify any underlying trends, seasonal patterns, or outliers in the disbursement figures.
Your Role: Your task is to leverage Power BI to create a bar chart that will visualize the sum of loans disbursed by month. The goal is to uncover any significant trends or deviations in the loan disbursement data, which may indicate opportunities or risks for financial institutions.
Data Overview: The dataset, BIData.xlsx, contains detailed records of loan disbursements, including the date of disbursement and the amount disbursed, alongside other relevant financial metrics.
Specific Challenges:
Identify outliers in the data using the 10th and 90th percentiles as thresholds to help in recognizing unusually low or high loan disbursements.
Determine if there are any seasonal patterns in loan disbursements that could inform the consortium's decision-making process.
Outcome: A well-structured bar chart that not only displays the sum of loans disbursed by month but also clearly highlights any outliers beyond the 10th and 90th percentiles. Your visual analysis will be crucial in the upcoming strategic meeting with the consortium.
For example, you might want to highlight a line indicating the minimum, mean or maximum values in the chart. In Power BI, the analytics pane captures all the analytical options available for any selected chart at your disposal. In this guide, you will learn how to explore and implement the analytics pane 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. The variables to be used in this guide are described below:
Loan_disbursed: Loan amount (in US dollars) disbursed by the bank.Month: Month of loan disbursal.
Start by loading the data.
Loading Data
Once you open the 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 Visualization
To begin, you will need a chart, table, or matrix. You will create a clustered column chart in this guide. You can locate it in the Visualizations pane. Click on the chart and it will create a chart box in the canvas. Nothing is displayed because you haven't yet added the required visualization arguments.
You can resize the chart on the canvas. The next step is to fill the visualization arguments under the Fields option as shown below. Drag the variable Month into the Axis field, and Loan_disbursed in the Values field.
The output above shows the Loan_disbursed by Month chart. You can perform some formatting options. For example, you can turn on the Data labels option as shown below.
You can also increase the size of the labels. Set the Text size to 12.
The desired chart is created, and you can now explore the analytics pane.
Analytics Pane
Once you have a chart selected, you might want to perform several analytical operations on it. To look at the options available, select the chart, and look at the Analytics option. This looks like a little magnifying glass.
You can see all the analytical options available for this chart.
Plotting Minimum and Maximum Lines to the Chart.
Click on the Min line option as shown above and click Add. You can change the Color and Transparency as shown below. This will create the resulting output.
Repeat the process for Max line option and you can keep a different color for contrast.
The above output shows that the minimum and maximum values are $24 million and $49 million, respectively.
Adding Average Line to the Chart
The minimum and maximum lines are good, but often as analysts you want to look at the average line. Click on the Average line and click Add. You can change the Color and Transparency as shown below. This will create the resulting output.
The average monthly loan disbursed stands at $37 million.
Adding Percentile Lines to the Chart
Outlier detection is an important task in business intelligence and machine learning. One of the common techniques of doing this is to cap the outlier points with certain percentile values. In this case, you will select the tenth and ninetieth percentile point for outlier detection.
Start by removing the lines by clicking on the x sign. This is not necessary, but you can do it to understand the percentile line better.
Once you have removed the lines, it will result in the following output.
Click on the Percentile line and click Add. Set the percentile value to 90.
The output shows that the line around the ninetieth percentile value is plotted in the chart.
To add the tenth percentile value, click again on Add as shown above, and set the percentile value to 10.
Now you have the chart with required percentile values. The values outside these two percentile lines will be considered outliers. In this case, the values $24 million, $48 million, and $49 million are outliers.
Conclusion
Analytical skills are one of the most sought after and in-demand skillsets of this decade. It is a sector agnostic skill and is used in every organization across sectors. Power BI Desktop provides many useful analytical features in the analytics pane, and this knowledge will help you strengthen your analytics and business intelligence capabilities.
Last updated
