W3SKILLSET

TrendCardsv5 Power BI Report

By /

Requirement Document for Power BI Report

1. Introduction

This document outlines the requirements for the Power BI report titled “TrendCardsv5”. The report is designed to provide insights into funding, settlements, law firm performance, and sales metrics.

2. Objective

The primary objective of this report is to:

  • Track total funding and settlements over a defined period.
  • Provide insights into sales performance by different salespersons.
  • Analyze law firm performance and trends.
  • Monitor funding breakdowns based on case types and funding types.
  • Provide financial insights using key performance indicators (KPIs).

3. Scope

The report includes the following sections:

  1. Executive Summary
    • Total Funded Amount
    • Total Settled Amount
    • Funding Type Distribution
    • Discounted vs. Full Payment Settlements
    • Number of Fundings by Year
  2. Law Firm Scorecard
    • Number of law firms by salesperson
    • Performance Trendcards (IRR, Avg Multiple, Avg Duration, etc.)
    • Case Type Breakdown
    • Settled Cases Analysis
  3. Sales Report
    • Total Funded vs. Previous Year (YoY Comparison)
    • Breakdown by Sales Code
    • Top 10 Law Firms by Funding Amount
    • Law Firms with Fundings Dropped ≥ 5%
  4. Settlement Analysis
    • Discounted vs. Full Payment Settlements
    • Settlements by Law Firm
    • Law Firms with Discounts ≥ 30%
  5. Funding Details
    • Case Funding Trends
    • Funding Requests and Approvals
    • Breakdown by Funding Type and Case Type
  6. Case Intake Analysis
    • Total Submissions vs. Funded Cases
    • Processing Time Statistics
    • Breakdown of Submission Actions

4. Data Sources

The Power BI report integrates data from:

  • Funding Database: Includes details on funding requests, approvals, and disbursements.
  • Sales Database: Tracks performance of salespersons and law firms.
  • Settlement Database: Stores information on case settlements and payment outcomes.
  • Law Firm Database: Contains details on law firms and their historical funding trends.

5. Functional Requirements

  • Filters & Drill-Downs: Users should be able to filter data by:
    • Date Range
    • Salesperson
    • Law Firm
    • Case Type
    • Funding Type
  • KPI Metrics: The report should display key metrics, including:
    • Total Funded Amount
    • Total Settled Amount
    • Internal Rate of Return (IRR)
    • Number of Fundings and Settlements
  • Visualization Requirements:
    • Bar Charts for funding trends
    • Pie Charts for settlement distributions
    • Tables for detailed law firm performance
    • Line Charts for sales trends over time

6. Security & Access Control

  • The report is restricted for internal use.
  • Only authorized personnel should access financial and settlement data.
  • Role-based access should be implemented to limit data visibility.

7. Performance Considerations

  • The Power BI report should handle large datasets efficiently.
  • Optimized DAX queries and data model should be used.
  • Data refresh should be scheduled periodically for up-to-date insights.

8. Assumptions & Constraints

  • Data should be accurate and updated from integrated sources.
  • Users should have Power BI access to view reports.
  • Some data points, such as settlements, may have confidentiality restrictions.
TrendCardsv5Download

Project Completion Method

1. Data Extraction (SQL Queries)

The first step is extracting the required data from the database using SQL queries. The queries will retrieve information about fundings, settlements, law firm performance, and sales.

Example SQL Queries

1.1 Fetching Total Funded and Settled Amounts

sqlCopyEditSELECT 
    SUM(funded_amount) AS TotalFunded,
    SUM(settled_amount) AS TotalSettled
FROM FundingData
WHERE funding_date BETWEEN '2008-10-01' AND '2024-08-02';

1.2 Fetching Sales Performance by Salesperson

sqlCopyEditSELECT 
    sales_person,
    SUM(funded_amount) AS TotalFunded,
    SUM(settled_amount) AS TotalSettled,
    COUNT(DISTINCT case_id) AS TotalCases
FROM SalesData
GROUP BY sales_person;

1.3 Fetching Law Firm Performance Metrics

sqlCopyEditSELECT 
    law_firm_name,
    COUNT(DISTINCT case_id) AS NumberOfCases,
    SUM(settled_amount) AS TotalSettledAmount,
    AVG(irr) AS AverageIRR
FROM LawFirmData
GROUP BY law_firm_name;

1.4 Fetching Case Type Breakdown

sqlCopyEditSELECT 
    case_type,
    COUNT(case_id) AS TotalCases,
    SUM(funded_amount) AS TotalFunded,
    SUM(settled_amount) AS TotalSettled
FROM CaseData
GROUP BY case_type;

2. Data Transformation (Power Query in Power BI)

After extracting data using SQL, we will clean and transform the data using Power Query Editor.

Power Query Steps:

  1. Remove Duplicates: Ensure that duplicate rows are removed for accurate reporting.
  2. Change Data Types: Convert data types where needed (e.g., DateTime → Date, String → Number).
  3. Merge Queries: Combine different datasets for relationships, such as linking SalesData with LawFirmData.
  4. Create Custom Columns: Add new computed columns such as funding duration, YoY difference, etc.
  5. Unpivot Data: Convert wide-format tables into long-format for better visual representation.
  6. Filter Data: Apply necessary filters to include only relevant data.
  7. Rename Columns: Ensure a consistent naming convention for ease of use in DAX.

Example Power Query Formula for Creating Funding Duration Column

powerqueryCopyEditFundingDuration = Duration.TotalDays([SettledDate] - [FundingDate])

3. Data Modeling (Power BI)

  • Establish Relationships between tables (Fact Table & Dimension Tables).
  • Create a Star Schema with:
    • Fact Tables: FundingData, SalesData, SettlementData
    • Dimension Tables: LawFirmData, SalesPersonData, CaseTypeData

4. Calculated Measures & KPIs (DAX)

Using DAX (Data Analysis Expressions), we create the necessary calculated measures for insights.

Example DAX Measures

4.1 Total Funded Amount

DAXCopyEditTotal Funded = SUM(FundingData[FundedAmount])

4.2 Total Settled Amount

DAXCopyEditTotal Settled = SUM(SettlementData[SettledAmount])

4.3 Year-over-Year (YoY) Growth

DAXCopyEditYoY Growth = 
VAR PreviousYear = CALCULATE([Total Funded], SAMEPERIODLASTYEAR(FundingData[FundingDate]))
RETURN DIVIDE([Total Funded] - PreviousYear, PreviousYear, 0)

4.4 Average IRR (Internal Rate of Return)

DAXCopyEditAverage IRR = AVERAGE(LawFirmData[IRR])

4.5 Funding Approval Rate

DAXCopyEditApproval Rate = DIVIDE(COUNT(FundingData[ApprovedAmount]), COUNT(FundingData[RequestedAmount]), 0)

4.6 Case Type Distribution

DAXCopyEditCaseType Percentage = 
DIVIDE(COUNT(CaseData[CaseID]), CALCULATE(COUNT(CaseData[CaseID]), ALL(CaseData)), 0)

5. Report Development & Visualization

  • Interactive Charts:
    • Bar Charts: Total Funded per Salesperson, Total Settled per Law Firm
    • Line Charts: Yearly Funding & Settlement Trends
    • Pie Charts: Case Type Distribution, Settlement Breakdown
    • Tables: Law Firm Performance Metrics, Salesperson Leaderboard
  • Filters & Slicers:
    • Time Period Selection
    • Salesperson Selection
    • Law Firm Selection
    • Case Type Filter
  • Tooltips & Drill-Throughs:
    • Drill-down into individual law firms for detailed analysis.
    • Hover tooltips for additional data insights.

6. Deployment & Optimization

6.1 Performance Optimization

  • Reduce dataset size by removing unused columns.
  • Optimize DAX calculations by using variables (VAR) to reduce redundant calculations.
  • Implement Aggregations to speed up calculations.

6.2 Data Refresh Schedule

  • The report should be scheduled for daily refresh to ensure up-to-date insights.

7. Security & Access Control

  • Role-Based Access: Restrict data based on user roles.
  • Data Encryption: Secure sensitive financial and legal data.
  • Audit Logging: Track changes and access logs for compliance.

8. Final Testing & Validation

  • Data Validation: Cross-check Power BI report data with SQL database.
  • User Acceptance Testing (UAT): Gather feedback from stakeholders.
  • Performance Testing: Ensure the report loads efficiently.

Conclusion

This method ensures the successful completion of the Power BI report using SQL Queries for data extraction, Power Query for transformation, and DAX for analytics. The report will provide actionable insights into funding, settlements, law firms, and sales performance.

sample data files in CSV format for the key datasets used in the Power BI report. These files will include:

  • FundingData.csv – Contains information on funded amounts, settled amounts, funding dates, and law firms.
  • SalesData.csv – Includes salesperson details, total funded amounts, and settled amounts.
  • LawFirmData.csv – Tracks law firm performance, cases handled, and settlement amounts.
  • CaseData.csv – Lists different case types and funding details.
FundingDataDownload
SalesDataDownload
LawFirmDataDownload
CaseDataDownload
Scroll to Top