Power BI - Electronics Distribution Analytics

A production-grade Power BI solution built from scratch as the sole analyst at an electronics distribution and retail company. The system consolidates sales, inventory, and sell-out data from SAP HANA and Excel sources into a unified star schema serving 6+ dashboard pages.

Note: Connection strings, file paths, employee names, and company identifiers have been anonymized. All M query logic and DAX patterns are real and production-tested.

---

Dashboards

Open Sales Orders

Tracks all open SOs with aging buckets (0-7, 8-14, 15-30, 30+ days), channel and customer breakdown, and top sales employees by open amount.

Sell Through

YoY sales performance across channels and categories, with waterfall chart showing category-level contribution to the YoY delta.

Demand & Stock Movement (Trends)

MoM demand trend line with dynamic toggle (By Quantity / By Amount), and weekly stock movement tracking internal vs. market stock vs. pure market stock.

Sell Out - AV

Weekly sell-out performance by channel with CY vs. LY comparison, YoY segment waterfall, and item-level sell-out table.

Market Stock Insights - AV

Internal and market stock split by segment, model-level stock table with aging buckets (by month), and stock distribution table showing WH/Sellable/Committed by retailer.

Internal Stock Insights - AV

Internal inventory deep-dive: segment stock value, distribution by subcategory, stock by model, quarterly stock movement by series, and stock in months.

---

Data Model - Star Schema

Three fact tables share conformed dimensions via a bridge table that handles the many-to-many customer/channel relationship.

                         ┌──────────────┐
                         │   dim_date   │
                         └──────┬───────┘
                                │
         ┌──────────────────────┼──────────────────────┐
         │                      │                      │
┌────────┴───────┐   ┌──────────┴──────────┐   ┌──────┴──────────┐
│ fact_Invoices  │   │  fact_SalesOrder    │   │  fact_OpenSO    │
└────────┬───────┘   └──────────┬──────────┘   └──────┬──────────┘
         │                      │                      │
         └──────────────────────┼──────────────────────┘
                                │
              ┌─────────────────┼────────────────┐
              │                 │                │
    ┌─────────┴────┐  ┌─────────┴──────┐  ┌─────┴──────────────────┐
    │   dim_item   │  │   dim_date     │  │  dim_CustomerChannel    │
    └──────────────┘  └────────────────┘  │  (Bridge)              │
                                          │  BridgeKey:            │
                                          │  CustCode|Channel|Sub  │
                                          └────────────────────────┘

Inventory & Sell-out tables connect via Item Code + WeekStartDate to dim_item and dim_date.

---

Repository Structure

powerbi-dist-analytics/
│
├── m-queries/
│   │
│   ├── sources/          # Raw data extraction - SAP HANA SQL + Excel readers
│   │   ├── fact_InvoiceSOINV_raw.pq    # Invoice + Return UNION via native HANA SQL
│   │   ├── fact_openso_raw.pq          # Open Sales Orders from HANA
│   │   ├── fact_salesorder_raw.pq      # Sales Orders + cancellations UNION
│   │   ├── Inventory_AV_Historical.pq  # Excel sheet reader (historical)
│   │   ├── Inventory_AV.pq             # Excel sheet reader (current)
│   │   ├── Inventory_Historical.pq     # Inventory tracker (historical)
│   │   ├── Inventory_Tracker.pq        # Inventory tracker (current)
│   │   ├── Sell_out_CY.pq              # Current-year sell-out with header detection
│   │   ├── Sell_out_Historical.pq      # Historical sell-out
│   │   ├── p_StartDate.pq              # Date range parameter
│   │   └── p_EndDate.pq                # Date range parameter
│   │
│   ├── transforms/       # Business logic layer - cleaning, classification, enrichment
│   │   ├── fact_InvoiceSOINV_clean.pq      # Channel classification + bridge key
│   │   ├── fact_openso_clean.pq            # SO aging buckets + channel classification
│   │   ├── fact_salesorder_clean.pq        # Channel classification + bridge key
│   │   ├── Inventory_CY-Hist.pq            # Append + PeriodKey + WeekStartDate
│   │   ├── Inventory_AV_CY-Hist.pq         # Append + PeriodKey + WeekStartDate
│   │   ├── InventoryMarketTracker__AV_.pq  # Internal vs. channel stock split
│   │   └── Sell_out_CY-Hist.pq             # Append + segment mapping + calendar alignment
│   │
│   ├── dimensions/       # Dimension tables
│   │   ├── dim_date.pq              # Full calendar with week logic + PeriodKey
│   │   ├── dim_item.pq              # Item master from SAP HANA + MainCategoryMap join
│   │   ├── dim_CustomerChannel.pq   # Bridge dim built from all three fact tables
│   │   ├── dim_SalesEmployee.pq     # Normalized name key, deduped before combine
│   │   ├── dim_MainCategory.pq      # Static DATATABLE - 12 clean category values
│   │   └── dim_SourceType.pq        # Disconnected slicer for dynamic measure switching
│   │
│   └── helpers/          # Shared logic - called by multiple queries
│       ├── ChannelFunctions.pq      # Central function library (record of 12 functions)
│       ├── CustomerChannelMap.pq    # Explicit channel override table
│       ├── MainCategoryMap.pq       # Raw SAP group name to clean category alias
│       ├── Toggle_View.pq           # Open SO / Invoices toggle slicer
│       ├── Trend_Qty-Amt.pq         # By Quantity / By Amount toggle (DAX field param)
│       └── Waterfall_Year.pq        # LY / CY toggle for waterfall chart
│
├── dax/
│   └── key_measures.md   # Selected DAX patterns with explanations
│
├── screenshots/          # Dashboard screenshots
│
└── README.md

---

Key Engineering Decisions

1. Centralized Channel Classification (ChannelFunctions)

All channel logic lives in one shared M query - a record of 12 named functions. All three fact tables call into it via Record.FromTable(ChannelFunctions). Any change propagates everywhere automatically with no duplication.

Functions exported: FxU (normalize), FxT (trim), FxCust (customer alias), FxIsCoop (co-op detection), FxCrown (crown channel), FxNormalizeSub (subchannel), FxMainChannel, FxMainSubchannel, FxRTLCode (retail store codes), FxApplyMC (main + transaction category split).

2. Bridge Table Pattern

The many-to-many customer/channel relationship is resolved via a composite key:

BridgeKey = CustomerCode & "|" & CrownChannel & "|" & Subchannel

dim_CustomerChannel is built by deduplicating across all three fact tables with priority: Invoices (1), Sales Orders (2), Open SOs (3). This ensures the most reliable channel classification wins.

3. Main Category vs. Transaction Category Split

Two separate columns resolve a real-world data quality issue in SAP:

• Main Category - what the item actually is (HA, AV, IT, etc.)
• Transaction Category - what business unit the transaction was booked under (e.g. a TV sold through an SDN cost center stays SDN in Transaction Category but HA in Main Category)

This allows filtering by product type AND by how the transaction was booked, which are legitimately different things in reporting.

4. Disconnected Slicer + Dynamic Measure Switching

dim_SourceType has no relationship to any fact table. A SELECTEDVALUE + SWITCH DAX pattern reads it and dynamically switches all measures between Invoices, Open SOs, Sales Orders, and combined views - one slicer controls the entire report.

5. W01 Edge Case Fix

Date.StartOfWeek(#date(yr, 1, 1), Day.Sunday) rolls back into the prior year's December when January 1 is not a Sunday, orphaning W01 from dim_date. Fixed consistently across all inventory and sell-out M scripts:

if wk = 1 then #date(yr, 1, 1)
else Date.AddDays(Date.StartOfWeek(#date(yr, 1, 1), Day.Sunday), (wk - 1) * 7)

6. SAP HANA Windowed Query Pattern

Raw fact tables use a shared helper function that wraps Value.NativeQuery with date-window parameters (<<StartDate>> / <<EndDate>>). This keeps the SQL templates clean and allows the date range parameters (p_StartDate, p_EndDate) to drive all three fact queries from one place.

7. Inventory Append Pattern

Each inventory source has a Historical + Current pair combined via Table.Combine after schema alignment with MissingField.UseNull. A defensive column detection block handles variant column names across export versions before building PeriodKey and WeekStartDate.

---

Setup

To connect this to your own data sources:

1. SAP HANA - replace <SAP_HANA_HOST:PORT> with your HANA endpoint in all sources/fact_*.pq and dimensions/dim_item.pq files.
2. Excel sources - replace \\<NAS_SERVER>\<SHARE_PATH>\ with your actual UNC path in all sources/Inventory_*.pq and sources/Sell_out_*.pq files.
3. Schema name - replace <SCHEMA> with your SAP Business One company schema name in all SQL blocks.
4. Excluded item code - replace <EXCLUDED_ITEM_CODE> with any service/dummy item codes to filter from transactions.
5. Helper queries - ChannelFunctions, CustomerChannelMap, MainCategoryMap, fn_HanaWindowedQuery, MappingTables must exist as separate queries in the same PBIX for the transforms to resolve.

---

Tech Stack

SAP HANA SAP Business One Power BI Desktop Power Query M DAX Python Excel