📊 Power BI Amazon Sales Analysis: A Complete Guide with DAX Queries

In this tutorial, you’ll learn how to build a full-fledged Power BI dashboard using Amazon-style marketplace data.
We’ll use sample data tables like Sellers, Products, Categories, and Sales
from multiple countries (Amazon USA, UK, UAE, India, KSA).
By the end, you’ll master DAX queries, filters, relationships, and best practices for building dynamic and interactive Power BI reports.

🧩 Table of Contents

1. Step 1: Understanding the Data Model
2. Step 2: Creating the Excel Data Files
3. Step 3: Importing into Power BI
4. Step 4: Defining Relationships
5. Step 5: Creating DAX Measures
6. Step 6: Filtering and Slicing Data
7. Step 7: Building Visuals and KPIs
8. Step 8: Business Insights and Interpretation
9. Step 9: Bonus Tips and Optimization

🔍 Step 1: Understanding the Data Model

Before diving into Power BI, it’s important to understand how the tables relate to each other.
Here’s our basic Amazon marketplace schema:

• Sellers: Contains seller ID, name, and country.
• Products: Product details including category and price.
• Categories: A simple lookup table of product categories.
• Sales: Includes product, seller, date, quantity, and country of sale.

  Sellers Table:
  ┌──────────┬───────────────┬──────────┐
  │ SellerID │ SellerName    │ Country  │
  ├──────────┼───────────────┼──────────┤
  │ S001     │ Global Bazaar │ USA      │
  │ S002     │ TechnoMart    │ UK       │
  │ ...      │ ...           │ ...      │

  Products Table:
  ┌───────────┬──────────────────┬────────────┬───────┐
  │ ProductID │ ProductName      │ CategoryID │ Price │
  ├───────────┼──────────────────┼────────────┼───────┤
  │ P001      │ Wireless Mouse   │ C01        │ 25    │
  │ P002      │ USB Charger      │ C02        │ 18    │

  Categories Table:
  ┌────────────┬──────────────────────┐
  │ CategoryID │ CategoryName         │
  ├────────────┼──────────────────────┤
  │ C01        │ Electronics          │
  │ C02        │ Mobile Accessories   │

  Sales Table:
  ┌────────┬────────────┬───────────┬──────────┬──────────┬──────────┐
  │ SaleID │ SaleDate   │ ProductID │ SellerID │ Quantity │ Country  │
  ├────────┼────────────┼───────────┼──────────┼──────────┼──────────┤
  │ SL001  │ 2025-01-01 │ P001      │ S001     │ 5        │ USA      │
  │ SL002  │ 2025-01-02 │ P002      │ S002     │ 2        │ UK       │
  

📥 Step 2: Creating the Excel Data Files

Prepare four Excel sheets based on the structure above. You can download ready-to-use sample files below:

• Download Sellers Table
• Download Products Table
• Download Categories Table
• Download Sales Table

🧮 DAX Query Examples: From Single to Multi-Table Logic

Let’s walk through a series of DAX measures starting from simple single-table logic and progressing to multi-table relational queries involving Sales, Products, Categories, and Sellers.

✅ 1. Total Quantity (Single Table)

Calculate the total quantity sold using only the Sales table.

Total Quantity = SUM(Sales[Quantity])

✅ 2. Total Sales (Flat Price – Single Table)

Assuming every product has a flat price of 25 (not recommended in real cases).

Total Sales (Flat Price) = SUM(Sales[Quantity]) * 25

✅ 3. Total Sales (Using Two Tables: Sales + Products)

Join Sales and Products using RELATED() to fetch the product price.

Total Sales = 
SUMX(
    Sales,
    Sales[Quantity] * RELATED(Products[Price])
)

✅ 4. Sales by Product

Group total sales by ProductID. Use Products[ProductName] in visuals.

Sales by Product = 
CALCULATE(
    [Total Sales],
    ALLEXCEPT(Sales, Sales[ProductID])
)

✅ 5. Sales by Category (Three Tables: Sales + Products + Categories)

To break sales down by CategoryName, use RELATED() and CALCULATE().

Sales by Category = 
CALCULATE(
    [Total Sales],
    ALLEXCEPT(Products, Products[CategoryID])
)

Optional calculated column (if needed):

CategoryName = RELATED(Categories[CategoryName])

✅ 6. Sales by Country (Four Tables)

Analyze sales based on the Country column in the Sales table.

Sales by Country = 
CALCULATE(
    [Total Sales],
    ALLEXCEPT(Sales, Sales[Country])
)

✅ 7. Sales by Seller

Group revenue by SellerID. Use Sellers[SellerName] in the visual.

Sales by Seller = 
CALCULATE(
    [Total Sales],
    ALLEXCEPT(Sales, Sales[SellerID])
)

✅ 8. Combined Analysis: Seller + Country + Category

Once the model is fully related, you can use the base measure Total Sales and apply it across multiple dimensions visually:

• Rows: Sellers[SellerName]
• Columns: Sales[Country]
• Filters/Slicers: Categories[CategoryName]

Total Sales = 
SUMX(
    Sales,
    Sales[Quantity] * RELATED(Products[Price])
)

📊 Summary Table

These are the foundational measures you’ll use for dashboards, KPIs, and category breakdowns in Power BI. As we progress, we can build more advanced logic with filters, dates, and ranking.

📊 Step 3: Importing into Power BI

Open Power BI Desktop and follow these steps:

1. Click Get Data → Choose Excel
2. Load all four tables into Power BI
3. Rename each table for consistency (e.g., tbl_Sellers, tbl_Products, etc.)

🔗 Step 4: Defining Relationships

Go to the Model View and define relationships between the tables:

• Sales[SellerID] → Sellers[SellerID]
• Sales[ProductID] → Products[ProductID]
• Products[CategoryID] → Categories[CategoryID]

🧮 Step 5: Creating DAX Measures

Now, let’s write some essential DAX formulas in the Sales table:

  Total Sales = SUMX(Sales, Sales[Quantity] * RELATED(Products[Price]))

  Total Quantity = SUM(Sales[Quantity])

  Sales by Country = 
  CALCULATE(
    [Total Sales],
    ALLEXCEPT(Sales, Sales[Country])
  )

  Revenue by Seller = 
  SUMX(
    Sales,
    Sales[Quantity] * RELATED(Products[Price])
  )
  

🔍 Step 6: Filtering and Slicing Data

Use slicers for:

• Country
• Product Category
• Sale Date (with between filter)
• Seller Name

Also create calculated columns like:

  Month = FORMAT(Sales[SaleDate], "MMM YYYY")
  

📈 Step 7: Building Visuals and KPIs

Create these visuals in your report:

• Card: Total Sales
• Bar Chart: Sales by Country
• Line Chart: Monthly Sales Trend
• Table: Top 5 Sellers by Revenue
• Pie Chart: Sales by Category

💡 Step 8: Business Insights and Interpretation

Ask key questions like:

• Which country generates the most revenue?
• Which product category performs best?
• Are sales increasing month-to-month?
• Which seller is the top performer?

Use bookmarks and drill-through features to add interactivity.

🚀 Step 9: Bonus Tips and Optimization

• Use SUMMARIZE and CALCULATE for advanced aggregation
• Optimize model by hiding unnecessary columns
• Create reusable measures instead of calculated columns
• Use themes and design templates for consistent branding

🎉 Conclusion

🧠  Understanding DAX Filtering Logic Step-by-Step

One of the most powerful features of DAX is its ability to manipulate filters dynamically using functions like CALCULATE(), FILTER(), ALL(), and others.
Let’s break down how filtering works in DAX and how you can use it to write smarter, more flexible measures.

🎯 1. Implicit vs Explicit Filters

Power BI visuals apply implicit filters based on slicers or axes. DAX can override or modify these using explicit filters inside a measure.

-- Example 1: Implicit Filter from Visual
Total Quantity = SUM(Sales[Quantity])
-- If a slicer filters "India", the measure only sums Indian rows

⚙️ 2. Using CALCULATE to Modify Filters

CALCULATE() evaluates an expression in a modified filter context.

-- Total Sales for UK only, regardless of slicer
Sales UK Only = 
CALCULATE(
  SUMX(Sales, Sales[Quantity] * RELATED(Products[Price])),
  Sales[Country] = "UK"
)

🧼 3. Removing Filters with ALL()

ALL() removes filters from a column or table. Use it when you want to ignore slicers or make comparisons.

-- Sales percentage of total (ignoring slicers)
% of Total Sales = 
DIVIDE(
  [Total Sales],
  CALCULATE([Total Sales], ALL(Sales))
)

📌 4. Keeping Filters with ALLEXCEPT()

ALLEXCEPT() removes all filters except the one(s) you specify.

-- Country-wise sales with all other filters removed
Sales by Country = 
CALCULATE(
  [Total Sales],
  ALLEXCEPT(Sales, Sales[Country])
)

🔍 5. Custom Row Context with FILTER()

FILTER() lets you define your own row filtering logic inside CALCULATE.

-- Sales for high-volume orders only
High Volume Sales = 
CALCULATE(
  [Total Sales],
  FILTER(Sales, Sales[Quantity] > 10)
)

🚫 6. Removing Specific Filters with REMOVEFILTERS()

REMOVEFILTERS() is a modern replacement for ALL when you want to be more readable.

-- Ignore Product Category filter
All Categories Sales = 
CALCULATE(
  [Total Sales],
  REMOVEFILTERS(Categories[CategoryName])
)

📈 7. Row Context vs Filter Context

Row context is when DAX evaluates row-by-row (e.g., calculated column). Filter context is what visuals or CALCULATE use.

-- Inside a visual table (filter context), this works:
Total Revenue = 
SUMX(Sales, Sales[Quantity] * RELATED(Products[Price]))

But in a calculated column, you’d already be in a row context and wouldn’t need CALCULATE unless bridging tables.

🧠 8. Nested CALCULATE and FILTER Use Case

-- Top-Selling Products Only
Top Products Sales = 
CALCULATE(
  [Total Sales],
  FILTER(
    Products,
    [Total Sales] > 1000
  )
)

This is useful in Top N analysis, dynamic thresholds, or category-level metrics.

✅ Summary

• CALCULATE() modifies filter context
• FILTER() adds row-level filtering logic
• ALL() and REMOVEFILTERS() remove filters for comparisons
• ALLEXCEPT() keeps specific filters for grouped totals
• Know the difference between row context and filter context

Mastering DAX filter logic is the key to unlocking advanced reporting and analytics in Power BI.