AtliQ's Journey: Building Scalable Sales Analytics with Excel and Power Pivot

In today’s data-driven business landscape, decision-makers rely heavily on accurate, intuitive, and actionable reports. In this project, I designed and implemented a comprehensive Sales Performance Analytics solution for AtliQ, utilizing Excel, Power Query, and Power Pivot. This project focused on transforming raw transactional data into meaningful insights through advanced data modeling, ETL processes, and user-centric reporting.

In this article, I'll walk you through the full end-to-end thought process, technical design, and business outcomes of creating two key reports:

• Customer Performance Report

• Market (Country) Performance Report

Step 1: Defining the Business Problem

We aimed to answer two critical questions:

1. How are individual customers performing year-over-year from 2019 to 2021?

2. How are different markets (countries) performing against their 2021 sales targets?

These reports would empower the business to:

✅ Identify top-performing customers and regions
✅ Track sales trends and growth
✅ Measure performance against strategic targets
✅ Make informed, timely decisions

Step 2: ETL (Extract, Transform, Load)

Imported Data Sources:

We began by importing four critical datasets into Power Query:

• dim_customer

• dim_market

• dim_product

• fact_sales_monthly

To ensure data quality, we leveraged Column Distribution and Column Quality tools to proactively identify and fix issues like missing values and inconsistencies.

Why ETL Matters:

In real-world analytics, clean data beats big data. Spelling errors in countries like “Philliphines” (corrected to Philippines) and "Newzealand" (New Zealand) could easily break relationships and lead to inaccurate reports. We corrected these early, avoiding downstream reporting errors.

Step 3: Data Modeling for Scalability

A robust data model is the backbone of insightful reports. Here’s how we designed ours:

Why add a dim_date table?

It enables dynamic time-based calculations, fiscal year tracking, and YOY comparisons. We generated this table directly in Power Query, ensuring it covered all required date ranges.

Step 4: Building the Customer Performance Report

The Customer Performance Report tracks net sales per customer from 2019 to 2021, along with year-over-year growth.

Key Features:

• Filters for Region, Market, and Division

• Net Sales by fiscal year

• YOY % Growth between 2020 and 2021

Technical Challenges:

We encountered a blank column when adding "FY" (Fiscal Year). After investigation, we discovered a mismatch between date formats. We resolved this by:

1. Creating a normalized date column (new_date_modified) in fact_sales_monthly.

2. Re-establishing relationships in Diagram View.

3. Refreshing data to sync formats.

Measures We Built:

net_sales = SUM(fact_sales_monthly[net_sales_amount])
net_sales_2019 = CALCULATE([net_sales], dim_date[FY] = "2019")
net_sales_2020 = CALCULATE([net_sales], dim_date[FY] = "2020")
net_sales_2021 = CALCULATE([net_sales], dim_date[FY] = "2021")
2021_vs_2020_growth = DIVIDE([net_sales_2021], [net_sales_2020], 0)

UX Enhancements:

• Conditional Formatting for visual trend spotting

• Custom number formats like "0.0,,M" to show figures in millions

• A clean, print-friendly Page Layout with headers and consistent fonts

Step 5: Building the Market Performance vs Targets Report

In this report, we analyzed actual vs. target sales for 2021 by country.

Steps:

1. Imported target data via Power Query.

2. Corrected country name inconsistencies.

3. Created connections with dim_market and dim_date.

4. Built these critical measures:

target_21 = SUM(ns_targets_2021[ns_target])
2021_Target_Difference = [net_sales_2021] - [target_21]
2021_Target_Percentage = DIVIDE([2021_Target_Difference], [target_21], 0)

This allowed stakeholders to immediately identify underperforming and overperforming markets.

Step 6: Additional Insights

To further optimize the business insights, we extended the project with:

✅ Top 10 Products by net sales growth

✅ Division-level growth reports
✅ Top 5 and Bottom 5 products by quantity sold

✅ New products launched in 2021
✅ Top 5 countries by net sales in 2021

These insights not only reinforced the model’s flexibility but demonstrated its ability to answer ad-hoc business questions.

Final Thoughts

This project allowed me to apply advanced Excel techniques to solve real-world business challenges through data. By building a complete Sales Performance Analytics solution, I was able to transform raw sales data into meaningful insights that help track customer growth, monitor market performance, and measure targets effectively.

From designing the data model to creating dynamic reports, this experience reinforced the importance of clean data, strong relationships, and clear, actionable reporting. Projects like this not only develop technical skills but also demonstrate the ability to deliver real value through analytics — a crucial part of driving smarter business decisions.

Call to Action

If you're working on similar projects, preparing for data roles, or just looking to sharpen your analytics skills with Excel, I'd love to connect and exchange ideas! Feel free to reach out, ask questions, or share your own experience — let’s grow together as data professionals.