Requirement
The client, a loans intermediary company required a report covering the range of business performance metrics to help them get better business information and make informed decisions.
Their main system had very limited reporting capabilities and downloaded spreadsheets could not give the information they needed.
Challenges
There were four data sources: three with details of the different stages of the loan proposals and one CRM extract with customer details. Complex queries were required to transform the four sources into a consistent data model in Power BI. Loan proposals had to be tracked through the process with multiple date fields representing the progression from initial application through to being paid out. Loan counts and values had different meanings depending on what date was used and KPI metrics needed to be anchored to a particular date field.
Solution
The solution had 14 queries, five parameters and created a model with four tables plus a Calendar table and a disconnected table to allow the values displayed on some visuals to be switched between different measures.
The queries made extensive use of merge, append, pivot and unpivot to create consistent tables for Proposals and Customers
The report consisted of seven pages covering overall performance, Customer details, an activity heatmap, some other business specifics and full detail drill down page. There were 66 measures ranging from simple base measures (e.g. SUM and COUNTROWS) through to more complicated time-intelligence functions.
There were 7 connections between the main Fact table and the Calendar, and many of the measures used USERELATIONSHIP to activate specific connections.
It was initially deployed as a standalone report before being incorporated into SharePoint. The source Excel workbooks were initially stored locally then moved to OneDrive.
Design Highlights
This report uses a lot of Query code to wrangle the data into a consistent model, and demonstrates how overlapping and seemingly inconsistent data can be transformed. It features a particularly useful heat map that enabled the user to easily identify customers who were using the service less than they had previously, and should therefore be given some focus.