Data_Analysis_Excel_Power_Query_DAX

This project demonstrates my end-to-end BI skills using Power Query, Power Pivot (DAX), and Excel Visualization. I built a dynamic and interactive sales performance dashboard for a multi-store coffee chain using raw data extracted from an ERP system.

Dataset Used

Coffee Shop Dataset

Project Objectives

• Analyze total sales performance over a 6-month period (Jan–Jun 2025)

• Identify top-performing products and categories driving revenue

• Compare store-wise performance to assess consistency and potential for expansion

• Detect sales trends over time, including monthly growth and quarterly comparisons

• Understand customer behavior by time of day to optimize operations and marketing

• Evaluate the average selling price per product and category to inform pricing strategies

• Dashboard Interaction View Project Dashboard

Process Overview

1. Data Source & ETL

Source: ERP system export.

Tool: Power Query, Excel

Steps:

    Cleaned and normalized Transactions, Product, Store, and Calendar tables.

    Derived Time of Day segmentation using conditional logic and DAX Measures.

    Generated Quarter, Month Name, and Month Index columns from transaction dates.

2. Data Model

Built a star schema with:

    Fact Table: Transactions

    Dimension Tables: Calendar, Store, Product

Relationships:

    Many-to-one joins between Transactions and each dimension.

Used DISTINCTCOUNT, CALCULATE, FILTER, AVERAGEX, and DATEADD to create robust DAX measures.

3. DAX Measures (Samples)

Total Sales Previous Period = =CALCULATE([Total Sales],DATEADD('Calendar'[transaction_date],-1,MONTH))

Total Sales Previous Corresponding Period = =CALCULATE([Total Sales],SAMEPERIODLASTYEAR('Calendar'[transaction_date]))

% of Growth = IF(ISBLANK([Total Sales PP]), BLANK(), ([Total Sales] - [Total Sales PP]) / [Total Sales PP])

Coffee Sales % = [Coffee Sales] / [Total Sales All]

DAX usage included context transition, CALCULATE filters, and time intelligence for trend analysis.

DAX Measure Window

Power Query Editor Window

4. Visualization

Tool: Excel PivotCharts with slicers and KPIs

Dashboards include:

    Monthly and quarterly sales trends

    Top categories/products

    Sales by store & time of day

    % sales growth, total sales, average product price

    Interactive slicers for time period & store location

Key Findings & Recommendations

Findings:

• Total Sales: £504,343 across 214,470 units

• Highest Sales by Category: Coffee (£176,629), 35% of all sales

• Peak Sales Time: Breakfast (49.3% of total sales)

• Q2 Growth: +63.1% vs Q1, major jump in May (31.9% MoM growth)

• All three stores performed evenly, showing consistency

Recommendations:

• Boost breakfast-time marketing (offers, staffing, loyalty).

• Replicate Q2 strategies to maintain momentum.

• Expand coffee and tea variants (60% of total category sales).

• Consider new store locations based on uniform performance.

• Investigate February dip for process/marketing gaps.

Skills Demonstrated

• Power Query for efficient ETL & calculated columns

• DAX for advanced KPIs & time intelligence (YoY, MoM, QoQ)

• Data modeling with star schema & relationships

• Excel Pivot Dashboarding with interactivity

• Business acumen in interpreting sales performance

Future Enhancements

• Migrate dashboard to Power BI for real-time cloud publishing

• Add forecasting and trendlines using DAX or Excel Add-ins

• Integrate customer or demographic data for segmentation

• Include profitability metrics (COGS, margins, etc.)