🏆 PHASE 2 · PROJECT 🟡 Intermediate PROJECT P2

Phase 2 Project: Executive Dashboard

⏱️ 3–5 hours build time

🏗️ 8 Build Steps

✅ 12-Item Checklist

This is your Phase 2 capstone project. You'll build a complete, production-quality Executive Dashboard for a fictional e-commerce company called "ShopNova" — applying every skill from Lessons 6–10: calculated KPI fields, multiple chart types, parameters, filter actions, and polished layout. This is the type of dashboard you'd present to a C-suite executive. No shortcuts — build it properly.

Phase 2 Project · Course Progress 48%

📋 Project Brief

ShopNova Executive Dashboard

🏢

Client: ShopNova E-Commerce

ShopNova is a mid-sized online retailer with operations across the US. The CEO wants a single-screen executive dashboard that shows overall business health at a glance — revenue, profit, growth trends, regional performance, and product category mix. She wants to be able to filter by date range and product category, and click any region to drill into its detail.

Dataset: Sample - Superstore

Use Tableau's built-in Sample - Superstore dataset. It contains:

📋

Orders Table

Order ID, Order Date, Ship Date, Ship Mode, Customer ID/Name, Segment, Country/City/State/Postal/Region, Product ID/Category/Sub-Category/Product Name, Sales, Quantity, Discount, Profit

👥

People Table

Person (Regional Manager name), Region. Used to show which manager is responsible for each region's performance.

🔄

Returns Table

Returned (Yes/No), Order ID. Join to Orders to flag returned orders and calculate return rate.

The dashboard should cover 4 years of data (2020–2023). All views should be filterable by year and category.

🧮 Step 2

Calculated KPI Metrics

Before building any visuals, create these calculated fields. They'll be the foundation of your KPI tiles and referenced across multiple sheets.

①

Profit Ratio

Formula: SUM([Profit]) / SUM([Sales]). Format as percentage. This is the overall profitability efficiency metric — what percentage of revenue becomes profit.

②

YoY Revenue Growth

Formula: (SUM([Sales]) - LOOKUP(SUM([Sales]), -1)) / ABS(LOOKUP(SUM([Sales]), -1)). This table calculation compares current year to previous year. Requires YEAR(Order Date) in the view to work correctly.

③

Avg Order Value

Formula: SUM([Sales]) / COUNTD([Order ID]). Divide total revenue by the count of distinct orders. Format as currency.

④

Return Rate

After joining Returns table: COUNTD(IF [Returned] = "Yes" THEN [Order ID] END) / COUNTD([Order ID]). What percentage of orders get returned? Format as percentage.

⑤

Customer Lifetime Value (CLV)

Formula: SUM([Sales]) / COUNTD([Customer ID]). Total revenue divided by unique customers. How much does each customer contribute on average? Format as currency.

🎯 Step 3

KPI Header Row — Big Number Tiles

Create 5 separate sheets, one per KPI. Each sheet shows a single large number with a secondary label. These will form the top row of the dashboard.

📌

Building a BAN (Big Ass Number) KPI Tile

1. Create a new sheet. 2. Drag your metric (e.g., SUM(Sales)) to Text in the Marks card. 3. Change mark type to Text. 4. Format the number: large font (36–48pt), bold, center aligned. 5. Add a second row with a smaller description label using a constant string in a calculated field or annotation. 6. Remove all axes, gridlines, row/column dividers — leave only the number.

Build these 5 KPI tiles:

KPI1

Total Revenue

SUM([Sales]) — format as $X.XM. Label: "Total Revenue". Background: subtle dark card. Add a sparkline using WINDOW_AVG if space allows.

KPI2

Total Profit

SUM([Profit]) — format as $X.XM. Color the number green if positive, red if negative using a calculated field color. Label: "Net Profit".

KPI3

Profit Ratio

Your Profit Ratio field — format as XX.X%. Target is typically 15%+ for retail. Label: "Profit Margin". Color: green if ≥15%, amber if 8–15%, red if <8%.

KPI4

Total Orders

COUNTD([Order ID]). Label: "Total Orders". Format with comma separator. Simple, clean.

KPI5

Avg Order Value

Your Avg Order Value field — format as $XXX. Label: "Avg Order Value". Helps executives understand order size trends.

📈 Step 4

Revenue Trend + Forecast Line Chart

This is the main chart — a continuous line showing monthly revenue from 2020–2023 with a 6-month forecast. This tells the CEO whether the business is growing or declining and where it's headed.

Build the Base Line Chart

Drag MONTH(Order Date) (continuous, green) to Columns. Drag SUM(Sales) to Rows. Mark type: Line. This gives you a monthly revenue trend.

Add Profit as a Dual Axis

Drag SUM(Profit) to the right side of the Y-axis to create a dual axis. Right-click the right axis → Synchronize Axis. Both Revenue and Profit show on the same scale. Give each a different color.

Enable Forecast

Go to Analysis menu → Forecast → Show Forecast. Tableau adds 6 months of exponential smoothing forecast with confidence bands. Right-click the forecast → Describe Forecast to see the model quality metrics.

Add a Reference Line for Annual Target

Create a parameter Annual Revenue Target (float, default 2,500,000). Right-click the Sales axis → Add Reference Line → Constant → [Annual Revenue Target]. Label it "Target".

🗂️ Steps 5 & 6

Category Treemap + Regional Map

Step 5 — Category Performance Treemap

New sheet. Use Show Me → Treemap with Sub-Category, Sales, and your Profit Ratio calculated field. Size = Sales, Color = Profit Ratio using Red-Blue diverging palette centered at zero. Labels show Sub-Category and Sales. Title: "Category Performance". This reveals which sub-categories drive revenue vs. which are margin drains.

Step 6 — Regional Performance Map

New sheet. Double-click State — Tableau auto-generates a filled map. Drag SUM(Sales) to Color (orange gradient). Drag Profit Ratio to Tooltip. Drag Region to Detail. This gives the CEO a geographic view of where the business is strongest. Name the sheet "Regional Map".

💡

Pro Tip: Add a Region Label Layer

Duplicate the Regional Map sheet. On the duplicate, change the aggregation to ATTR(Region) on Label and change mark type to Circle. Combine both map layers as a dual-axis map: the bottom layer = filled states by sales, the top layer = region label circles. This creates a clean annotated map.

🎛️ Steps 7 & 8

Parameters + Filter Actions

Step 7 — Add Parameters

Date Range Parameter

Create parameter Start Year (integer, values: 2020, 2021, 2022, 2023). Create End Year similarly. Create a calculated field: In Date Range = YEAR([Order Date]) >= [Start Year] AND YEAR([Order Date]) <= [End Year]. Add to Filters on all sheets.

Category Selector

Create parameter Category Focus (string, list: "All", "Furniture", "Office Supplies", "Technology"). Create calculated field: Category Filter = IF [Category Focus] = "All" THEN TRUE ELSE [Category] = [Category Focus] END. Apply to all sheets.

Step 8 — Link All Views with Filter Actions

Map → All Charts Filter

Dashboard → Actions → Add Action → Filter. Source: Regional Map. Targets: ALL other sheets. Run on: Select. Clearing: Show all values. Name: "Region Drill-down".

Treemap → Trend Line Filter

Add another filter action. Source: Category Treemap. Target: Revenue Trend chart. This lets the CEO click a product category in the treemap to see that category's revenue trend in isolation.

Show Parameter Controls

Right-click each parameter → Show Parameter Control. Add them to the dashboard as a control panel on the right side. Format with labels so users understand what each control does.

✅ Project Checklist

Mark Your Progress

Check off each item as you complete it. Your score updates automatically.

0 / 12 completed

Data connected — Sample Superstore with Orders, People, and Returns tables joined

Verify all three tables are visible in Data Source view

5pts

5 calculated fields created: Profit Ratio, YoY Growth, Avg Order Value, Return Rate, CLV

All fields verified with no red error indicators in Data pane

10pts

5 KPI tile sheets built (Revenue, Profit, Margin, Orders, AOV)

Each is a clean Text mark with large formatted number, no axes or gridlines

10pts

Revenue trend line chart with Profit dual axis built

Continuous month on X-axis, synchronized dual axis

10pts

Tableau forecast enabled on the trend chart (6-month horizon)

Confidence bands visible, forecast described and verified

5pts

Category treemap built with Sales size and Profit Ratio color (diverging palette)

Red = losing money, blue = profitable. Sub-Category labels visible.

10pts

Regional filled map built with Sales color gradient

All US states rendering correctly, Profit Ratio in tooltip

10pts

Date Range parameters created and applied to all sheets

Changing Start/End Year filters all views simultaneously

10pts

Category Selector parameter created and applied to all sheets

"All" shows all categories; selecting "Technology" filters all views to tech data only

10pts

Map → All Charts filter action configured and tested

Clicking a state on the map filters KPIs, trend, and treemap to that region

10pts

Treemap → Trend chart filter action configured

Clicking a sub-category in treemap shows that category's revenue trend

5pts

Dashboard assembled, titled "ShopNova Executive Dashboard", professionally formatted

Clean layout, consistent fonts/colors, parameter controls visible, no sheet titles showing inside dashboard (use custom text boxes instead)

15pts