“Power BI KPI Dashboard – 20 Ways to Design & Visualize KPIs”

Requirements Document for Power BI Report

This Power BI report showcases 20 different ways to visually present Key Performance Indicators (KPIs) effectively. The report aims to provide business professionals, analysts, and dashboard designers with various approaches to displaying KPIs in an engaging and informative manner.

Functional Requirements

1. Data Sources & Integration

• The report must integrate structured data sources, including:
  • Sales data
  • Profit and revenue data
  • KPI benchmarks and historical comparisons
• 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
  • Percentage growth compared to the previous year (PY)
  • Month-over-month (MoM) growth
• Profit Metrics
  • Total Profit
  • Percentage growth vs. PY and MoM trends
• Profit Ratio
  • Percentage profit relative to sales
  • Breakdown by category (Furniture, Office Supplies, Technology)
• Target Achievement
  • Sales & profit values compared against predefined targets
  • Percentage completion towards targets

3. Data Visualizations & Layout

• The report must contain 20 different visualization styles, including:
  • Standard bar and line charts
  • Trend line comparisons
  • KPI scorecards with percentage change
  • Heat maps for category-based performance
  • Dynamic gauges and bullet charts
  • Decomposition trees to analyze KPI drivers
  • Comparative performance visuals (current year vs. previous year)
• Use of Power BI’s interactive capabilities:
  • Slicers for filtering data (e.g., by product category, region, and time period)
  • 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
• Dashboard Designers
• 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:

Target Data (target_data.csv)

Sales Data (sales_data.csv)

Product Category Data (product_category.csv)

Developed by Muhammad Zahid