60|POWERBI – Looking for a Loan

BYU Student Author: @DylanKing
Reviewers: @IWillyerd, @Alex_Garrett
Estimated Time to Solve: 20 Minutes

We provide the solution to this challenge using:

  • Power BI

Need a program? Click here.

Overview
Assume the year is 2020. You and a friend recently founded a software startup and it has been doing well. With the onslaught of the Covid-19 pandemic, the growth in your company has unfortunately begun to slow down dramatically. You talk it over with your friend and you figure that it would be a good time to receive a loan to help accelerate the growth of the business.

You learn about the PPP Loan which is a loan that the government (SBA or small business administration) is backing to help small businesses keep their workforce employed during the COVID-19 crisis. You have received the data for the number of PPP Loans that businesses within the state of Utah have qualified for. You want to use Microsoft Power BI to help your friend see that the data shows that you have a good chance of qualifying for a loan.

Instructions
You want to create 5 simple data visualizations for your friend.

  1. The first two are very simple. Create two cards. The first card will be for the number of lenders granting PPP Loans in your state.
  2. The second card will be for the number of PPP Loans that have been granted. These two visualizations will provide a quick general understanding of the PPP Loan market in Utah.
  3. Next, create a filled map. Use your filled map to calculate the number of loans per city in Utah. Use a gradient to see which cities are getting the most PPP Loans and which are getting the least. Assume you and your business are located in Provo, UT. Zoom in on your city and find out how many businesses in your city have qualified for a PPP Loan.
  4. Then, create a decomposition tree. Begin with the total PPP Loans. Then break that up by NAICS Description. Since you are a software startup, move down to software publishers and click on that. Then, break that up by the lender to see which lenders are providing the most loans to software publishing businesses in Utah. This information will be crucial to creating the following visualization.
  5. Lastly, from the previous visualization you now know which lender provides the most loans to software publishing businesses. Create a clustered column chart for that lender which highlights the number of loans as a percentage of the total loan population for each loan range. Assume, your friend is hoping to qualify for a loan in the range of 1 – 2 million dollars. To highlight, color only this range. This visualization should easily show you what percentage of this lender’s loans are in that range.

Data Files

Suggestions and Hints
  • I had to create a new record ID column to solve this. One way you can do that on Power BI is to edit the query by clicking on transform data, then add column, and then index column.
  • In connection with the third visualization, you should find that there were 224 PPP Loans given in Provo.
  • In connection with the fourth visualization, you should find that the lender that provides the most loans to software publishing businesses in Utah is Zion’s Bank.

Solution