Build a Global KPI Dashboard

Use SQL and Power BI to build an executive KPI dashboard from 100K rows.

Introduction

30 Second Summary

Every company tracks its health through a handful of key numbers. Revenue trends, profit margins, and customer retention rates tell executives whether the business is growing or bleeding money.

In this project, you will take a 100,000-row global sales dataset, write SQL queries to calculate five core business KPIs, and build an interactive Power BI dashboard that surfaces insights at a glance.

What You'll Build

A complete data analytics pipeline that transforms raw transaction data into an executive-level interactive dashboard.

By the end of this project, you'll have:

  • A DuckDB database with SQL queries calculating Revenue, Profit Margin, Customer Acquisition Cost, Retention Rate, and Inventory Turnover.
  • A multi-page Power BI report with KPI cards, trend lines, regional drilldowns, cross-filtering, and an anomaly alert table.
  • A documented anomaly detection query that flags sudden revenue drops exceeding 20% month-over-month and drills into root causes.
  • Secret Mission: Add What-If parameter analysis and conditional formatting for executive-level presentation.

Are there any prerequisites?

You'll need a basic understanding of SQL (SELECT, WHERE, GROUP BY) and familiarity with spreadsheets or data tables. No prior experience with DuckDB or Power BI is required.

Set Up Your Analytics Environment

Before you can write SQL queries or build dashboards, you need two tools installed on your machine.

In this step, you'll install DuckDB (a lightweight SQL engine that runs locally) and Power BI Desktop (your visualization tool). You'll also download the raw sales dataset that powers the entire project.

In this step, get ready to:
  • Install the DuckDB CLI on your machine.
  • Install Power BI Desktop.
  • Download the Global Sales Dataset and set up your project folder.
Install DuckDB CLI

DuckDB is an analytical database that runs entirely on your local machine. It requires no server setup and can query CSV files directly using standard SQL.

  • Press the Windows key to open your search bar.
  • Type Command Prompt and press Enter to open it.
  • Install DuckDB using the Windows package manager by running this command:
winget install DuckDB.cli

The installer will download and set up DuckDB. This may take a minute.

  • Verify the installation by running this command:
duckdb --version

✔️ I see a version number

You should see something like v1.5.2 printed in your terminal. DuckDB is ready to go.

ⓧ Command not found

If you see an error saying the command is not recognized, try these fixes:

  • Close your Command Prompt window completely, then reopen it. The winget installer adds DuckDB to your PATH, but existing terminal sessions do not pick up the change automatically.
  • If it still fails, your machine may be missing the Microsoft Visual C++ Redistributable. Download and install it from the official Microsoft page, then try again.
  • If winget itself is not recognized, make sure you are running Windows 10 (version 1809 or later) or Windows 11. The winget tool comes pre-installed with the App Installer package from the Microsoft Store.

What is DuckDB?

DuckDB is designed for analytics workloads. Think of it as a local SQL engine that runs in a single file with no configuration. It reads CSV, Parquet, and JSON files natively.

The SQL you write in DuckDB (CTEs, window functions, JOINs) translates directly to any enterprise database like PostgreSQL or SQL Server. It is the fastest way to prototype analytics queries without infrastructure overhead.

Install Power BI Desktop

Power BI Desktop is a free Windows application for building interactive data visualizations. You'll use it to turn your SQL outputs into an executive-level dashboard.

  • Press the Windows key to open your search bar.
  • Type Microsoft Store and press Enter to open the Microsoft Store app.
  • In the Microsoft Store search bar at the top, type Power BI Desktop and press Enter.
  • Click Install (or Get) on the Power BI Desktop page.
  • Wait for the download and installation to complete. This is approximately 636 MB so it may take several minutes.

Why the Microsoft Store?

The Microsoft Store version auto-updates in the background and does not require admin rights to install. You can also download the standalone installer (PBIDesktopSetup_x64.exe) from the Microsoft Download Center if you prefer, but the Store method is simpler.

  • Once the installation finishes, press the Windows key, type Power BI Desktop, and press Enter to launch it.
  • Confirm that the Power BI Desktop splash screen appears and the app opens to a blank report canvas.
  • Close Power BI Desktop for now. You'll return to it in Step 5.

Power BI Desktop won't open?

Make sure your machine meets the minimum requirements: Windows 10 or later, at least 2 GB of RAM (4 GB recommended), and a display resolution of at least 1440x900.

If you see a blank screen or a WebView2 error, reinstall WebView2 from the Microsoft website and try launching Power BI Desktop again.

Help me troubleshoot Power BI Desktop not opening on my Windows machine.

Download the Global Sales Dataset

The dataset you'll analyze contains 100,000 rows of global sales transactions. It includes fields for revenue, profit margin, customer segments, countries, and more.

  • First, create the project folder structure by running these commands in your Command Prompt:
mkdir C:\Projects\kpi-dashboard\data
mkdir C:\Projects\kpi-dashboard\output
  • Confirm the folders exist by running:
dir C:\Projects\kpi-dashboard

You should see data and output listed as directories.

  • Open your browser and go to the dataset page on Hugging Face:

https://huggingface.co/datasets/mahfuzmee-eng/global-sales-dataset

  • Click on the Files and versions tab.
  • Download the CSV file (approximately 27 MB).
  • Save it to C:\Projects\kpi-dashboard\data\ using the filename as-is.

What is in this dataset?

The Global Sales Dataset from Hugging Face contains transaction-level data with columns for revenue_usd, profit_margin_pct, customer_id, country, channel, customer_segment, and pre-calculated RFM scores.

These fields map directly to the five KPIs you'll calculate: Revenue, Profit Margin, Customer Acquisition Cost, Retention Rate, and Inventory Turnover.

  • Verify the file is in place by running:
dir C:\Projects\kpi-dashboard\data

You should see the CSV file listed (approximately 27 MB).

Don't see the CSV file?

Make sure you saved the download to the correct folder path. Check that the file is in C:\Projects\kpi-dashboard\data\ and not in your default Downloads folder.

If the download from Hugging Face prompts you to sign in, create a free account first. The dataset is publicly available under the MIT license.

Help me download the Global Sales Dataset from Hugging Face into the right folder.

Your analytics environment is ready. Next up, you'll load the dataset into DuckDB and start exploring the data with SQL queries.

Load and Explore Data with SQL

Your analytics environment is ready. Now it's time to put it to work.

Before you can calculate business KPIs, you need to understand the data you're working with. Raw datasets often contain cancelled orders, missing values, and other noise that would throw off your metrics. In this step, you'll import the Global Sales Dataset into DuckDB, explore its structure, and create a clean view that filters out the junk.

In this step, get ready to:
  • Import the 100,000-row CSV into a DuckDB database.
  • Explore the schema and check for data quality issues.
  • Create a filtered view of clean transactions for analysis.
Create a DuckDB database and import the CSV

DuckDB can run as an in-memory database or persist data to a file on disk. You'll use a persistent database so your tables survive between sessions.

  • Press the Windows key to open your search bar.
  • Type cmd and press Enter to open Command Prompt.
  • Launch DuckDB with a persistent database file by running this command:
duckdb C:\Projects\kpi-dashboard\analytics.duckdb

You should see the DuckDB version number and a D prompt appear. This means you're inside the DuckDB interactive shell.

What is a persistent database?

By passing a filename to the duckdb command, DuckDB saves all tables, views, and data to that file on disk. Next time you open the same file, everything you created will still be there.

DuckDB not found?

If you see an error that duckdb is not recognized, close your Command Prompt and reopen it. The winget install from Step 1 may need a fresh terminal session to update your PATH.

If it still fails, DuckDB on Windows requires the Microsoft Visual C++ Redistributable. Most Windows 10/11 machines have this pre-installed, but you can download it from Microsoft if needed.

Help me fix a DuckDB launch error on Windows.

Now that you're inside DuckDB, you can import the CSV. DuckDB can read CSV files directly and create a table from them in a single command.

  • Create the sales table from your downloaded CSV by running this SQL:
CREATE TABLE sales AS
SELECT * FROM read_csv_auto('C:\Projects\kpi-dashboard\data\global-sales-dataset.csv');
  • Verify the import was successful by running:
SELECT count(*) AS total_rows FROM sales;

You should see 100000 in the output. This confirms all rows from the CSV loaded into your database.

What does this SQL do?

  • CREATE TABLE sales AS creates a new table called sales in your database.
  • read_csv_auto(...) reads the CSV file and automatically detects column names and data types from the header row.
  • The SELECT * grabs every column and row from the CSV and feeds it into the new table.

Seeing a file not found error?

Double check that your CSV file is saved at the exact path C:\Projects\kpi-dashboard\data\global-sales-dataset.csv. The filename must match exactly, including capitalization and hyphens.

Help me fix a file path error in DuckDB on Windows.

Explore the schema and data quality

Before calculating KPIs, you need to know what columns are available and whether any critical data is missing. A quick exploration now prevents confusing results later.

  • View all column names and their data types by running:
DESCRIBE sales;

You'll see a list of all columns in the table, including revenue_usd, country, transaction_date, profit_margin_pct, and many more. These are the building blocks for your KPI calculations.

Next, check whether any of the critical columns have missing values. NULL values in key fields like revenue or date would corrupt your KPI calculations.

  • Check for NULL values in the columns you'll use for KPIs by running:
SELECT
    count(*) - count(revenue_usd) AS missing_revenue,
    count(*) - count(country) AS missing_country,
    count(*) - count(transaction_date) AS missing_date,
    count(*) - count(profit_margin_pct) AS missing_margin
FROM sales;

What does this query do?

The count(*) function counts all rows, while count(column_name) counts only non-NULL values in that column. The difference tells you exactly how many rows are missing data in each field.

If you see zeros across the board, the dataset has no missing values in these critical columns.

Finally, check the date range and geographic scope of the dataset.

  • Preview the time span and number of countries by running:
SELECT
    min(transaction_date) AS earliest,
    max(transaction_date) AS latest,
    count(DISTINCT country) AS num_countries
FROM sales;

Why check date range and country count?

Knowing the date range tells you how many months of data you have for trend analysis. The country count confirms you have enough geographic diversity for regional comparisons in your dashboard.

Filter to clean transactions

Real transaction data includes cancelled orders and edge cases that would skew your KPIs. A SQL view lets you define a filtered version of the table without duplicating data.

  • Create a view that keeps only valid transactions by running:
CREATE VIEW clean_sales AS
SELECT *
FROM sales
WHERE order_status != 'Cancelled'
  AND revenue_usd > 0
  AND quantity > 0;

What does this view do?

A view is a saved query that acts like a virtual table. Every time you query clean_sales, DuckDB re-runs these filters on the underlying sales table.

The three filters remove cancelled orders (which never generated real revenue), rows with zero or negative revenue, and rows with zero or negative quantity. This gives you a trustworthy base for all KPI calculations going forward.

  • Verify the view was created and check how many clean rows remain:
SELECT count(*) AS clean_rows FROM clean_sales;

You should see a number lower than 100,000. The difference represents cancelled orders and invalid records that have been filtered out.

Getting a syntax error?

Make sure you included the semicolons at the end of each SQL statement. DuckDB won't execute a statement until it sees the ; character.

Also check that the single quotes around 'Cancelled' are straight quotes, not curly quotes. Some text editors auto-replace these.

Help me fix a SQL syntax error in DuckDB.

✔️ Awesome, I've got everything!

Great. Double check you see the D prompt and your count query returned results.

ⓧ I'd like to double check my database

Run this command to see all tables and views in your database:

SHOW TABLES;

You should see both sales and clean_sales listed. If either is missing, re-run the corresponding CREATE TABLE or CREATE VIEW statement from earlier in this step.

Your data is loaded, explored, and filtered. Next up, you'll write SQL queries that calculate the five core business KPIs from this clean dataset.

Calculate Core Business KPIs with SQL

Your data is loaded and cleaned. Now it's time to transform those 100,000 transactions into the metrics that matter to a business.

In this step, you'll write SQL queries that calculate five core business KPIs (Key Performance Indicators): Revenue, Profit Margin, Customer Acquisition Cost, Retention Rate, and Inventory Turnover. These are the metrics a CFO reviews every morning to check the company's health across regions.

In this step, get ready to:
  • Calculate revenue and profit margin metrics grouped by country and month.
  • Build customer acquisition cost and retention rate analysis using CTEs.
  • Create an inventory turnover proxy based on purchase frequency.
Calculate revenue and profit margin by region and month

The most fundamental KPIs for any business are total revenue and profit margin. Grouping them by country and month lets you spot regional trends over time.

  • In the DuckDB CLI from earlier, create the monthly_kpis view by running this query:
-- Revenue and margin metrics grouped by country and time period
CREATE VIEW monthly_kpis AS
SELECT
    country,
    year,
    month,
    SUM(revenue_usd) AS total_revenue,
    AVG(profit_margin_pct) AS avg_profit_margin,
    COUNT(DISTINCT customer_id) AS unique_customers,
    COUNT(*) AS total_orders,
    SUM(revenue_usd) / COUNT(DISTINCT customer_id) AS revenue_per_customer
FROM clean_sales
GROUP BY country, year, month
ORDER BY country, year, month;

What does this query do?

This view calculates five metrics for every country-month combination in your dataset.

  • SUM(revenue_usd) gives total sales dollars for that region and month.
  • AVG(profit_margin_pct) averages the per-transaction profit margins.
  • revenue_per_customer divides total revenue by unique customers to measure spending intensity.

The clean_sales view you built in the previous step automatically filters out cancelled orders and invalid records.

  • Verify the view works by running this preview query:
SELECT * FROM monthly_kpis LIMIT 20;

You should see 20 rows showing country names, year/month values, and calculated metrics like total_revenue and avg_profit_margin.

Seeing an error about the view already existing?

If you ran the query once and need to re-run it (for example, after spotting a typo), DuckDB won't let you create a view with the same name twice. Run DROP VIEW monthly_kpis; first, then re-run the CREATE VIEW statement.

Also check that you're querying clean_sales (the view) and not sales (the raw table). If clean_sales doesn't exist, go back to Step 2 and create it first.

Help me debug my monthly_kpis view in DuckDB

Calculate Customer Acquisition Cost and Retention Rate

Knowing how many customers are new versus returning tells you whether growth is coming from acquisition or loyalty. This query uses a CTE (Common Table Expression) to first identify each customer's earliest purchase, then classify every transaction as new or repeat.

  • Create the customer_metrics view by running this query:
-- Customer acquisition and retention analysis using CTEs and window functions
CREATE VIEW customer_metrics AS
WITH first_purchase AS (
    SELECT
        customer_id,
        MIN(transaction_date) AS first_purchase_date
    FROM clean_sales
    GROUP BY customer_id
),
customer_status AS (
    SELECT
        s.year,
        s.month,
        COUNT(DISTINCT CASE WHEN s.transaction_date = fp.first_purchase_date THEN s.customer_id END) AS new_customers,
        COUNT(DISTINCT CASE WHEN s.transaction_date != fp.first_purchase_date THEN s.customer_id END) AS returning_customers,
        COUNT(DISTINCT s.customer_id) AS total_active_customers
    FROM clean_sales s
    JOIN first_purchase fp ON s.customer_id = fp.customer_id
    GROUP BY s.year, s.month
)
SELECT
    year,
    month,
    new_customers,
    returning_customers,
    total_active_customers,
    ROUND(returning_customers * 100.0 / total_active_customers, 2) AS retention_rate_pct,
    ROUND(SUM(new_customers) OVER () * 1.0 / COUNT(*) OVER (), 0) AS avg_new_customers_per_period
FROM customer_status
ORDER BY year, month;

What does this query do?

This is the most complex query in the project. It works in three stages:

  • first_purchase CTE finds the earliest transaction date for every customer.
  • customer_status CTE compares each transaction's date to the customer's first purchase. If they match, the customer is "new" that month. Otherwise they're "returning."
  • The final SELECT calculates retention_rate_pct as the percentage of active customers who are returning. The OVER () window function computes an average across all periods.

This gives you a CAC proxy. A real CAC calculation would divide marketing spend by new customers. Since this dataset doesn't include marketing spend, the new-customer count per period serves as the acquisition volume metric.

  • Verify the view by previewing the results:
SELECT * FROM customer_metrics LIMIT 10;

You should see rows with year, month, new_customers, returning_customers, and a retention_rate_pct value between 0 and 100.

Query taking a long time or returning unexpected results?

The JOIN between clean_sales and first_purchase matches on customer_id. If you see zero returning_customers, double-check that you typed != (not equals) in the second CASE WHEN condition.

If you get a syntax error, check that both CTEs are separated by a comma and that the closing parenthesis after each CTE has no trailing comma before the final SELECT.

Help me debug my customer_metrics CTE query

Create a frequency-based inventory turnover proxy

True inventory turnover requires stock-level data, which this dataset doesn't have. Instead, you'll calculate how many units each product sells per month on average. High-velocity products indicate fast turnover.

  • Create the product_velocity view by running this query:
-- Purchase frequency as inventory turnover proxy
CREATE VIEW product_velocity AS
SELECT
    product,
    COUNT(*) AS total_transactions,
    SUM(quantity) AS total_units_sold,
    SUM(revenue_usd) AS total_revenue,
    ROUND(SUM(quantity) * 1.0 / COUNT(DISTINCT month || '-' || year), 2) AS avg_units_per_month
FROM clean_sales
GROUP BY product
ORDER BY avg_units_per_month DESC;

What does this query do?

This view groups all transactions by product and calculates how quickly each one moves.

  • total_transactions counts how many orders include this product.
  • total_units_sold sums the quantity field across all orders.
  • avg_units_per_month divides total units by the number of distinct year-month periods the product appeared in. Products at the top of the list are your highest-velocity items.
  • Verify the view by previewing the top sellers:
SELECT * FROM product_velocity LIMIT 10;

You should see product names ranked by avg_units_per_month in descending order, with the fastest-moving products at the top.

Seeing unexpected values in avg_units_per_month?

The expression SUM(quantity) * 1.0 forces a decimal division. If you omit the * 1.0, DuckDB performs integer division and you'll get rounded-down whole numbers.

If you see only one row or a single product, make sure GROUP BY product is present and spelled correctly.

Help me fix my product_velocity query in DuckDB

Why a proxy instead of real inventory turnover?

Real inventory turnover divides cost of goods sold by average inventory value. This dataset contains transaction data only, with no warehouse stock levels.

Purchase frequency (units sold per month per product) is a practical proxy that answers the same business question: which products move fastest? In an interview, explain this design decision and note that the SQL pattern would adapt easily to a dataset with inventory fields.

✔️ Awesome, I've got everything!

Great. Double check that all three views exist by running:

SELECT table_name FROM information_schema.tables WHERE table_type = 'VIEW';

You should see clean_sales, monthly_kpis, customer_metrics, and product_velocity listed.

ⓧ I'd like to double check the full code

Here are all three CREATE VIEW statements from this step. Run them in order inside your DuckDB CLI:

CREATE VIEW monthly_kpis AS
SELECT
    country,
    year,
    month,
    SUM(revenue_usd) AS total_revenue,
    AVG(profit_margin_pct) AS avg_profit_margin,
    COUNT(DISTINCT customer_id) AS unique_customers,
    COUNT(*) AS total_orders,
    SUM(revenue_usd) / COUNT(DISTINCT customer_id) AS revenue_per_customer
FROM clean_sales
GROUP BY country, year, month
ORDER BY country, year, month;

CREATE VIEW customer_metrics AS
WITH first_purchase AS (
    SELECT
        customer_id,
        MIN(transaction_date) AS first_purchase_date
    FROM clean_sales
    GROUP BY customer_id
),
customer_status AS (
    SELECT
        s.year,
        s.month,
        COUNT(DISTINCT CASE WHEN s.transaction_date = fp.first_purchase_date THEN s.customer_id END) AS new_customers,
        COUNT(DISTINCT CASE WHEN s.transaction_date != fp.first_purchase_date THEN s.customer_id END) AS returning_customers,
        COUNT(DISTINCT s.customer_id) AS total_active_customers
    FROM clean_sales s
    JOIN first_purchase fp ON s.customer_id = fp.customer_id
    GROUP BY s.year, s.month
)
SELECT
    year,
    month,
    new_customers,
    returning_customers,
    total_active_customers,
    ROUND(returning_customers * 100.0 / total_active_customers, 2) AS retention_rate_pct,
    ROUND(SUM(new_customers) OVER () * 1.0 / COUNT(*) OVER (), 0) AS avg_new_customers_per_period
FROM customer_status
ORDER BY year, month;

CREATE VIEW product_velocity AS
SELECT
    product,
    COUNT(*) AS total_transactions,
    SUM(quantity) AS total_units_sold,
    SUM(revenue_usd) AS total_revenue,
    ROUND(SUM(quantity) * 1.0 / COUNT(DISTINCT month || '-' || year), 2) AS avg_units_per_month
FROM clean_sales
GROUP BY product
ORDER BY avg_units_per_month DESC;

Your KPI calculations are ready. Next up, you'll write anomaly detection queries that automatically flag sudden revenue drops and drill into what caused them.

Build Anomaly Detection Queries

Your KPI views are calculating the right numbers, but a dashboard isn't truly useful until it can alert you to problems. A CFO doesn't want to manually scan tables looking for trouble.

In this step, you'll write SQL that automatically detects revenue anomalies using window functions. You'll also export all your prepared data to CSV files that Power BI can consume.

In this step, get ready to:
  • Detect month-over-month revenue drops exceeding 20% by region.
  • Write a root cause drill-down query to identify which segments caused a drop.
  • Export all views to CSV files for Power BI.
Detect month-over-month revenue changes by region

A 20% revenue drop in a single month could signal a lost client, a supply chain issue, or a seasonal dip. Window functions let you compare each month's revenue to the previous month without writing a self-join.

  • Create the revenue_anomalies view by running this query in your DuckDB CLI:
CREATE VIEW revenue_anomalies AS
WITH monthly_revenue AS (
    SELECT
        country,
        year,
        month,
        SUM(revenue_usd) AS revenue
    FROM clean_sales
    GROUP BY country, year, month
),
with_change AS (
    SELECT
        country,
        year,
        month,
        revenue,
        LAG(revenue) OVER (PARTITION BY country ORDER BY year, month) AS prev_month_revenue,
        ROUND(
            (revenue - LAG(revenue) OVER (PARTITION BY country ORDER BY year, month))
            * 100.0 / LAG(revenue) OVER (PARTITION BY country ORDER BY year, month),
            2
        ) AS mom_change_pct
    FROM monthly_revenue
)
SELECT *
FROM with_change
WHERE mom_change_pct < -20
ORDER BY mom_change_pct ASC;

What does this query do?

The first CTE (monthly_revenue) aggregates total revenue by country, year, and month from your clean_sales view.

The second CTE (with_change) uses the LAG() window function to look at the previous month's revenue for the same country. It then calculates the percentage change between consecutive months.

The final SELECT filters to only rows where the change is worse than -20%, meaning a significant revenue drop occurred.

  • Preview the anomalies by running:
SELECT * FROM revenue_anomalies LIMIT 10;

You should see rows showing countries, months, and the percentage drop that triggered the anomaly flag.

Seeing zero rows?

If no rows appear, the dataset might not have any drops exceeding 20%. Try changing the threshold from -20 to -10 in the WHERE clause to confirm the view works, then change it back.

Also confirm that clean_sales has data by running SELECT count(*) FROM clean_sales;.

Help me debug why my revenue_anomalies view returns no rows

Write a root cause drill-down query

Once an anomaly is flagged, the next question is always "why did this happen?" A good analyst doesn't just detect the problem. They identify which customer segment or sales channel caused it.

  • Run this drill-down query to investigate a flagged anomaly (replace the country, year, and month values with ones from your anomaly results):
SELECT
    country,
    year,
    month,
    customer_segment,
    channel,
    SUM(revenue_usd) AS segment_revenue,
    COUNT(*) AS order_count
FROM clean_sales
WHERE country = 'United States'  -- Replace with flagged country
  AND year = 2023               -- Replace with flagged year
  AND month IN (5, 6)           -- Compare the drop month to prior month
GROUP BY country, year, month, customer_segment, channel
ORDER BY year, month, segment_revenue DESC;

What does this query do?

This query breaks down revenue by customer_segment and channel for two consecutive months in a specific country. By comparing the two months side-by-side, you can spot which segment or channel shrank.

For example, if the Enterprise segment dropped from $500K to $200K while others stayed flat, you've found your root cause.

You should see a table with revenue broken down by segment and channel for both months, making it easy to compare where the drop originated.

Adapt this query to your results

Replace 'United States', 2023, and (5, 6) with values from your actual revenue_anomalies results. Use the flagged month and the month before it to compare.

Export all data for Power BI

Power BI needs to read your SQL results. The simplest approach is exporting each view to a CSV file that Power BI's native Text/CSV connector can import.

  • Export all four views to CSV by running these commands in DuckDB:
COPY (SELECT * FROM monthly_kpis) TO 'C:\Projects\kpi-dashboard\output\monthly_kpis.csv' WITH (HEADER true);
COPY (SELECT * FROM customer_metrics) TO 'C:\Projects\kpi-dashboard\output\customer_metrics.csv' WITH (HEADER true);
COPY (SELECT * FROM product_velocity) TO 'C:\Projects\kpi-dashboard\output\product_velocity.csv' WITH (HEADER true);
COPY (SELECT * FROM revenue_anomalies) TO 'C:\Projects\kpi-dashboard\output\revenue_anomalies.csv' WITH (HEADER true);

What do these commands do?

Each COPY statement runs a SELECT against one of your views and writes the results to a CSV file with column headers. DuckDB automatically creates the output directory if it doesn't exist.

These four files become the data source for your Power BI report in the next step.

  • Confirm the files were created by exiting DuckDB and listing the output folder:
dir C:\Projects\kpi-dashboard\output\

You should see four CSV files listed: monthly_kpis.csv, customer_metrics.csv, product_velocity.csv, and revenue_anomalies.csv.

Files not appearing?

Make sure you ran the COPY commands inside the DuckDB CLI (not in a regular terminal). You should see the DuckDB prompt (D) before each command.

If you see a "Permission denied" error, close any program that might have the output folder open and try again.

Help me fix my DuckDB COPY export that isn't creating CSV files

Your SQL analytics layer is complete. Next up, you'll bring these numbers to life in an interactive Power BI dashboard.

Build the Power BI Executive Dashboard

Your SQL analytics layer is complete. You have four CSV files containing clean, calculated KPIs ready for visualization.

Now it's time to turn those numbers into something a CFO can actually read at a glance. In this step, you'll import your SQL outputs into Power BI Desktop, create DAX measures for dynamic calculations, and build an Executive Summary page with interactive visuals.

In this step, get ready to:
  • Import your four CSV exports into a Power BI data model.
  • Create DAX measures for Total Revenue, Avg Profit Margin, and Revenue MoM %.
  • Design an Executive Summary page with KPI cards, trend lines, and anomaly alerts.
Import your data into Power BI Desktop

Power BI needs your SQL output files loaded as tables before you can build any visuals. You'll import all four CSVs into the data model.

  • Switch to Power BI Desktop.
  • Click Home in the ribbon at the top.
  • Click Get Data, then select Text/CSV.
  • Navigate to C:\Projects\kpi-dashboard\output\monthly_kpis.csv and click Open.
  • In the preview window, verify the column names and data look correct.
  • Click Load to bring the table into your data model.

Why Load instead of Transform Data?

Your SQL queries already cleaned and shaped the data. Clicking Load imports the CSV directly. Transform Data opens Power Query Editor for additional cleaning, which you don't need here.

  • Repeat the same process for the remaining three files:
  • Click Home > Get Data > Text/CSV.
  • Import customer_metrics.csv and click Load.
  • Import product_velocity.csv and click Load.
  • Import revenue_anomalies.csv and click Load.

You should now see all four tables listed in the Data pane on the right side of the screen: monthly_kpis, customer_metrics, product_velocity, and revenue_anomalies.

Don't see all four tables?

Make sure you clicked Load (not Cancel or Transform Data) on each import. Check the Data pane on the right. If a table is missing, repeat Get Data > Text/CSV for that specific CSV file.

help me troubleshoot missing tables in my Power BI data model

Create DAX measures for your KPIs

DAX measures let Power BI calculate values dynamically as users interact with filters and slicers. Instead of static numbers, your dashboard will recalculate KPIs on the fly when someone filters by country or time period.

  • In the Data pane on the right, click on the monthly_kpis table to select it.
  • Click the Modeling tab in the ribbon, then click New Measure.
  • In the formula bar that appears above the canvas, replace the default text with the Total Revenue measure by typing:
Total Revenue = SUM(monthly_kpis[total_revenue])
  • Press Enter to save the measure.
  • Click New Measure again and enter the Avg Profit Margin measure:
Avg Profit Margin = AVERAGE(monthly_kpis[avg_profit_margin])
  • Press Enter to save the measure.

What do these measures do?

SUM(monthly_kpis[total_revenue]) adds up all revenue values in the filtered context. If a user selects only "United States" in a slicer, the measure recalculates to show US revenue only.

AVERAGE(monthly_kpis[avg_profit_margin]) works the same way for profit margins. Both measures respond automatically to any filter applied on the report.

Now create the more complex Revenue MoM % measure that calculates month-over-month change.

  • Click New Measure and enter this formula:
Revenue MoM % = 
VAR CurrentRevenue = [Total Revenue]
VAR PreviousRevenue = CALCULATE([Total Revenue], DATEADD(monthly_kpis[month], -1, MONTH))
RETURN DIVIDE(CurrentRevenue - PreviousRevenue, PreviousRevenue, 0)
  • Press Enter to save the measure.

What does Revenue MoM % calculate?

This measure stores the current month's revenue in CurrentRevenue, then uses CALCULATE with DATEADD to fetch last month's revenue into PreviousRevenue.

DIVIDE then computes the percentage change. The final 0 argument prevents division-by-zero errors when no previous month exists.

Seeing an error with DATEADD?

The DATEADD function expects a date-type column. If your month column imported as an integer, the measure may not work as expected. You can still proceed. The KPI cards and other visuals will work correctly with the first two measures. The MoM measure becomes useful once you create a proper date table or adjust the formula later.

help me fix the DATEADD error in my Revenue MoM DAX measure

You should now see three measures with calculator icons under the monthly_kpis table in the Data pane: Total Revenue, Avg Profit Margin, and Revenue MoM %.

Design the Executive Summary page

The Executive Summary is the first page stakeholders see. It shows the most critical numbers at a glance and highlights anomalies that need attention.

  • Click on Page 1 at the bottom of the canvas (it may already be selected).
  • Double-click the page tab and rename it to Executive Summary.

Start by adding KPI cards across the top of the page.

  • In the Visualizations pane on the right, click the Card visual icon (a rectangle with a large number).
  • Drag Total Revenue from the Data pane into the card.
  • Resize and position the card in the top-left area of the canvas.
  • Add a second Card visual and drag Avg Profit Margin into it.
  • Add a third Card visual. Expand the customer_metrics table and drag retention_rate_pct into it.
  • Arrange all three cards in a row across the top of the page.

Format your card display units

Select a card, then in the Format visual pane (paintbrush icon), expand Callout value and set Display units to Millions for revenue or Percentage for margins. This keeps the numbers readable at a glance.

Next, add a Revenue Trend Line Chart below the cards.

  • Click an empty area of the canvas to deselect any visual.
  • In the Visualizations pane, click the Line Chart icon.
  • From the monthly_kpis table, drag year and month to the X-axis field well.
  • Drag total_revenue to the Y-axis field well.
  • Drag country to the Legend field well.
  • Resize the chart to fill the middle section of the page.

Why add country to the Legend?

Placing country in the Legend creates a separate line for each country. This lets you compare regional revenue trends side by side on the same chart.

Now add a Profit Margin by Country bar chart with conditional formatting.

  • Click an empty area of the canvas.
  • In the Visualizations pane, click the Clustered Bar Chart icon.
  • Drag country to the Y-axis field well.
  • Drag avg_profit_margin to the X-axis field well.
  • Position the bar chart in the bottom-left area of the page.

Apply conditional formatting so margins below 20% appear red and margins above 40% appear green.

  • Select the bar chart visual.
  • Click the Format visual icon (paintbrush) in the Visualizations pane.
  • Expand Bars (or Columns) and find the Color option.
  • Click the fx button next to Color to open conditional formatting.
  • Set Format style to Rules.
  • Add a rule: if value is less than 20, set color to red.
  • Add a rule: if value is greater than 40, set color to green.
  • Click OK to apply.

What does conditional formatting show?

Red bars immediately draw attention to countries with thin profit margins (below 20%). Green bars highlight healthy markets (above 40%). This visual encoding lets a CFO spot underperforming regions without reading numbers.

Finally, add an Anomaly Alert Table to surface the revenue drops your SQL query flagged.

  • Click an empty area of the canvas.
  • In the Visualizations pane, click the Table visual icon.
  • From the revenue_anomalies table, drag these columns into the Columns field well: country, month, revenue, and mom_change_pct.
  • Position the table in the bottom-right area of the page.

Add conditional formatting to highlight the severity of each drop.

  • Select the table visual.
  • In the Visualizations pane, right-click the mom_change_pct field and select Conditional formatting, then Background color.
  • Set the format to a color scale where more negative values show darker red.
  • Click OK to apply.
  • Save your report by pressing Ctrl+S.
  • Navigate to C:\Projects\kpi-dashboard\ and name the file Global_KPI_Dashboard.pbix.
  • Click Save.

Your Executive Summary page should now show three KPI cards at the top, a multi-line revenue trend chart in the middle, a color-coded profit margin bar chart on the bottom-left, and a red-highlighted anomaly table on the bottom-right.

Visuals showing blank or zero?

If a card or chart shows no data, confirm the correct field is in the right field well. Click the visual, then check the Build visual pane (bar chart icon) to see which fields are assigned. Make sure you're dragging from the correct table.

If the table visual shows zero rows, your revenue_anomalies.csv file may be empty. Return to DuckDB and verify the export contains data.

help me debug my Power BI visuals that are showing blank or zero values

Your Executive Summary is ready to impress. Next up, you'll add a Regional Drilldown page with slicers and cross-filtering to let stakeholders explore the data themselves.

Add Regional Drilldown and Interactivity

Your Executive Summary page gives stakeholders a high-level view of business health. Now you need a way for them to dig deeper into specific regions without leaving the report.

In this step, you'll build a Regional Drilldown page with interactive filtering, create a custom tooltip that appears on hover, and apply professional formatting across the entire report.

In this step, get ready to:
  • Create a Regional Drilldown page with a country slicer, revenue breakdown chart, and product velocity table.
  • Add cross-filtering and a custom tooltip page to your Revenue Trend Line Chart.
  • Apply a consistent theme, titles, and number formatting across all pages.
Create the Regional Drilldown page

A dedicated drilldown page lets users isolate one country at a time and see exactly which customer segments and sales channels drive revenue there.

  • In Power BI Desktop, click the + icon at the bottom of the canvas (in the page tabs area) to add a new report page.
  • Right-click the new page tab and select Rename.
  • Type Regional Drilldown and press Enter.

Now add the three visuals that make up this page.

  • With nothing selected on the canvas, click the Slicer icon in the Visualizations pane.
  • In the Data pane, expand the monthly_kpis table and drag country into the slicer's Field well.
  • Position the slicer on the left side of the page so it acts as a filter panel.

What is a slicer?

A slicer is a visual filter that sits directly on the report canvas. When a user selects a country in the slicer, every other visual on the page automatically filters to show only that country's data.

  • Click an empty area of the canvas, then select the Stacked bar chart icon in the Visualizations pane.
  • Drag customer_segment from monthly_kpis to the Y-axis.
  • Drag total_revenue to the X-axis.
  • Drag channel from the clean_sales source (or monthly_kpis if available) to the Legend well.
  • Click another empty area of the canvas, then select the Table icon in the Visualizations pane.
  • From the product_velocity table, drag these fields into the Columns well: product, total_units_sold, total_revenue, and avg_units_per_month.

What does this page show?

The slicer lets users pick a country. The stacked bar chart reveals which customer segments and channels generate the most revenue in that country. The table shows product velocity so stakeholders can see which products move fastest.

  • Select a country in the slicer (for example, United States) and confirm that both the bar chart and the table update to reflect that country's data.

Slicer not filtering the other visuals?

Make sure all three visuals are on the same page. Slicers only filter visuals on their own page by default.

If the table still shows all products, confirm you loaded product_velocity.csv as a separate table in Step 5. The slicer filters by country, which must exist in the table's data model relationships.

Why isn't my slicer filtering the product velocity table?

Add cross-filtering and tooltips

Cross-filtering is built into Power BI by default. Clicking a segment in one chart highlights related data in every other visual on the page. Tooltip pages take this further by surfacing a mini-report when users hover over a data point.

  • On the Regional Drilldown page, click any bar segment in the Stacked Bar Chart.
  • Observe that the product velocity Table automatically highlights or filters to match the selected segment.

What is cross-filtering?

When you click a data point in one visual, Power BI automatically filters or highlights related data in the other visuals on the same page. This is the default behavior and requires no extra configuration.

Now create a tooltip page that appears when hovering over your Revenue Trend Line Chart on the Executive Summary page.

  • Click the + icon at the bottom of the canvas to add another new page.
  • Click an empty area on the new page so nothing is selected.
  • In the Format pane (paint roller icon), expand Canvas settings and set the Type dropdown to Tooltip. This resizes the canvas to a small tooltip-optimized size.
  • In the Format pane, expand Page information and turn the Tooltip slider to On.
  • Rename this page to Revenue Tooltip by right-clicking the page tab.
  • Add a Card visual to the tooltip page.
  • Drag revenue_per_customer from the monthly_kpis table into the Card's field well.
  • Add a small Line chart visual below the Card.
  • Drag month to the X-axis and total_revenue to the Y-axis to create a small revenue trend.

Now assign this tooltip to the Revenue Trend Line Chart on the Executive Summary page.

  • Switch back to your Executive Summary page by clicking its tab.
  • Select the Revenue Trend Line Chart visual.
  • In the Visualizations pane, click the Format visual icon (paint roller), then expand General and scroll to Tooltips.
  • Set Type to Report page.
  • In the Page dropdown, select Revenue Tooltip.
  • Hover over a data point on the Revenue Trend Line Chart. You should see the tooltip page appear with the revenue-per-customer Card and the small trend chart.

Tooltip page not appearing on hover?

Confirm the Tooltip slider is set to On in the tooltip page's Page information section. Without this, Power BI does not register the page as a tooltip.

Also check that the Revenue Trend Line Chart's tooltip Type is set to Report page (not Default) and the correct page is selected.

Why isn't my tooltip page showing when I hover?

Format and polish your report

Consistent formatting gives your dashboard a professional look. Applying a theme ensures all pages share the same color palette, fonts, and visual styling.

  • Click the View tab in the ribbon at the top of Power BI Desktop.
  • Click Themes and select a professional theme from the gallery (for example, a dark or corporate theme).
  • The theme applies to all pages in your report automatically.
  • On the Executive Summary page, click Insert in the ribbon, then select Text box.
  • Type Global KPI Dashboard - Executive Summary and position the text box at the top of the page.
  • Repeat on the Regional Drilldown page with the title Global KPI Dashboard - Regional Drilldown.
  • Select any revenue Card or chart visual on the Executive Summary page.
  • In the Format pane, expand General and then Data format. Set the format to Currency for revenue fields.
  • For profit margin fields, set the format to Percentage.
  • Apply the same formatting to corresponding visuals on the Regional Drilldown page.
  • Save your report by pressing Ctrl+S (Windows).
  • If prompted for a save location, save to C:\Projects\kpi-dashboard\Global_KPI_Dashboard.pbix.

Your report should now have three page tabs: Executive Summary, Regional Drilldown, and Revenue Tooltip. Each page has a title, consistent theme colors, and properly formatted numbers.

Theme not applying to all visuals?

If individual visuals still show old colors, select the visual and check whether a manual color override was applied. In the Format pane under the visual's color settings, reset any custom colors to let the theme take effect.

How do I reset visual colors to match my Power BI theme?

Secret mission

What-If Analysis and Conditional Formatting

Your dashboard shows current KPIs, but executives want to answer "what if" questions. What happens to profitability if Customer Acquisition Cost increases by 15%? Add a What-If parameter that lets stakeholders simulate changing business conditions with a slider, plus conditional formatting that instantly highlights when metrics cross critical thresholds.

Clean Up Your Resources

Clean Up Your Resources

This project runs entirely locally on your machine, so there are no ongoing cloud costs or subscriptions to worry about. Decide whether to keep your resources running, pause them to come back later, or delete them entirely.

Resources you used:

  • DuckDB CLI (installed via winget).
  • Power BI Desktop (installed via Microsoft Store or direct download).
  • Project folder at C:\Projects\kpi-dashboard\ containing your database, CSV files, and Power BI report.

Keep everything running

No action needed. Choose this if you're still actively building or plan to extend this dashboard with new data sources.

  • Your analytics.duckdb file stays on disk and can be opened anytime with duckdb C:\Projects\kpi-dashboard\analytics.duckdb.
  • Your Power BI report (Global_KPI_Dashboard.pbix) opens directly by double-clicking.
  • DuckDB CLI and Power BI Desktop remain installed on your system.

Pause - I'll come back to this later

Since everything is local, there are no running processes to stop. Your files sit on disk without consuming resources until you open them again.

  • Close Power BI Desktop if it's still open.
  • Close any DuckDB CLI sessions by typing .exit and pressing Enter.
  • Everything stays installed and ready for when you return.

Delete - I don't want to use this again

Remove all project resources and uninstall the tools completely.

  • Delete the project folder by opening a terminal and running:
  • Close Power BI Desktop if it's still open before deleting.
rmdir /s /q C:\Projects\kpi-dashboard
  • Uninstall DuckDB CLI by running:
winget uninstall DuckDB.cli
  • Uninstall Power BI Desktop through Windows Settings.
  • Open Settings.
  • Select Apps, then Installed apps.
  • Search for Power BI Desktop, click the three-dot menu, and select Uninstall.

Nice Work!

Nice Work!

You just built a complete Data Analyst portfolio piece from scratch. Starting from a 100,000-row global sales dataset, you wrote SQL to calculate core business KPIs, detected anomalies, and presented everything in an interactive Power BI dashboard that a CFO could use to monitor company health.

You've learned how to:

  • Load and clean raw transaction data with DuckDB SQL, then calculate 5 core business KPIs (Revenue, Profit Margin, CAC, Retention Rate, and Inventory Turnover proxy) using CTEs, window functions, and aggregations.
  • Build anomaly detection queries with window functions that flag sudden revenue drops exceeding 20% and drill into the root cause by segment and channel.
  • Design a multi-page interactive Power BI dashboard with KPI cards, trend lines, regional drilldowns, cross-filtering, and tooltip pages.
  • Add What-If parameter analysis and conditional formatting to simulate changing business conditions and present executive-level insights.

This project demonstrates exactly the skills listed in the Global Data Flow Operator job description: gathering data from multiple sources, writing SQL to combine and analyze it, designing automated dashboards, and detecting anomalies with root-cause analysis.

Ready to quiz yourself?