Power BI KPI Dashboard Sales, Profit & Performance Insights

Requirements Document for Power BI Report

This Power BI report is designed to track key performance indicators (KPIs) related to sales, profit, and profit ratio. The dashboard provides insights into monthly and yearly trends, category-wise performance, and comparisons against targets and previous performance metrics.

Functional Requirements

1. Data Sources & Integration

• The report must integrate structured data sources, including:
  • Sales transactions data
  • Profit and revenue data
  • Category-wise sales breakdown
• Data should be sourced from SQL databases, Excel files, or cloud-based sources (e.g., Azure, Google Sheets).
• Automated data refresh to ensure up-to-date insights.

2. KPI Metrics and Calculations

• Sales Performance
  • Total Sales
  • Year-over-Year (YoY) percentage growth
  • Month-over-Month (MoM) growth
• Profit Metrics
  • Total Profit
  • Profit percentage growth vs. previous year and MoM trends
• Profit Ratio
  • Percentage profit relative to sales
  • Breakdown by category (Furniture, Office Supplies, Technology)
• Comparative Analysis
  • Current vs. Previous Year (PY) trends
  • MoM percentage changes
  • YoY growth indicators

3. Data Visualizations & Layout

• The report must contain various visualization styles, including:
  • Bar and line charts for trends
  • KPI scorecards with percentage changes
  • Category-wise performance breakdowns
  • Sales and profit comparison visuals (YoY, MoM, vs. PY)
  • Interactive slicers for filtering by product category and time period
• Use of Power BI’s interactive capabilities:
  • Drill-through functionality for deeper insights
  • Tooltips with additional context when hovering over charts

4. User Interface & Experience

• Consistent color scheme and branding
• Intuitive navigation with buttons and bookmarks
• Responsive design for different screen sizes
• Clear labeling and annotations for easy interpretation

Non-Functional Requirements

• Performance: The report should load within 5 seconds for standard queries.
• Security: Implement row-level security (RLS) to ensure users only see authorized data.
• Scalability: Should support future data expansion without performance degradation.
• Accessibility: Ensure compliance with WCAG guidelines for visual accessibility.

Technical Specifications

• Power BI Version: Power BI Desktop (latest version)
• Data Refresh Frequency: Daily
• Storage Requirements: Cloud-based storage integration (OneDrive, SharePoint, or SQL Server)
• Deployment Mode: Published on Power BI Service with sharing permissions for stakeholders.

Stakeholders

• Business Analysts
• Sales and Finance Teams
• Executives & Decision-Makers
• IT & Data Teams

Delivery Timeline

• Phase 1: Data integration & visualization setup – Week 1-2
• Phase 2: User testing and feedback implementation – Week 3
• Phase 3: Deployment and documentation – Week 4

Project Completion Method

1. SQL Queries for Data Extraction

• Extract sales data:

SELECT OrderDate, ProductCategory, SalesAmount, Profit FROM SalesTable WHERE OrderDate >= '2023-01-01';

• Aggregate profit by category:

SELECT ProductCategory, SUM(SalesAmount) AS TotalSales, SUM(Profit) AS TotalProfit FROM SalesTable GROUP BY ProductCategory;

• Monthly sales trend:

SELECT YEAR(OrderDate) AS Year, MONTH(OrderDate) AS Month, SUM(SalesAmount) AS TotalSales FROM SalesTable GROUP BY YEAR(OrderDate), MONTH(OrderDate) ORDER BY Year, Month;

2. Power Query for Data Transformation

• Clean and format sales data
• Merge multiple data sources for consistency
• Remove duplicates and handle missing values
• Convert text-based date fields to Date format for proper visualization

3. Power Query M Language

• Filter data for the last year dynamically:

let Source = Sql.Database("ServerName", "DatabaseName"), SalesTable = Source{[Schema="dbo", Item="SalesTable"]}[Data], FilteredRows = Table.SelectRows(SalesTable, each [OrderDate] >= Date.AddYears(DateTime.LocalNow(), -1)) in FilteredRows

4. DAX for KPI Calculations

• Total Sales:

TotalSales = SUM(SalesTable[SalesAmount])

• Sales Growth %:

SalesGrowth = VAR PreviousSales = CALCULATE(SUM(SalesTable[SalesAmount]), PREVIOUSYEAR(SalesTable[OrderDate])) RETURN DIVIDE(SUM(SalesTable[SalesAmount]) - PreviousSales, PreviousSales, 0)

• Profit Ratio:

ProfitRatio = DIVIDE(SUM(SalesTable[Profit]), SUM(SalesTable[SalesAmount]), 0)

• Target Achievement %:

SalesToTarget = DIVIDE(SUM(SalesTable[SalesAmount]), [SalesTarget], 0)

Here are three sample data files in CSV format for Power BI report:

1. Sales Data (sales_data.csv)
2. Product Category Data (product_category.csv)
3. Target Data (target_data.csv)

Developed by Muhammad Zahid