Live Power BI dashboard

Published report walmart_visualization — Executive, Store, Product, and Demand pages on Gold aggregates. Open in Power BI ↗

Analysis pages: 08 Executive · 09 Store · 10 Product · 11 Demand · Overview

Part A — All DAX measures (paste into _Measures)

Complete catalogue of 33 Power BI measures used across all dashboard pages. Create each with right-click _Measures → New measure, then paste. Format currency / percentage measures in the ribbon after creating them.

Core sales

/* 1 */
Total Units =
SUM ( 'agg_daily_store_sales'[units_sold] )

/* 2 — format Currency $ */
Total Sales Value =
SUM ( 'agg_daily_store_sales'[sales_value] )

/* 3 — format Currency $ */
Avg Sell Price =
AVERAGE ( 'agg_daily_store_sales'[avg_sell_price] )

/* 4 */
Active SKUs (Store Day) =
SUM ( 'agg_daily_store_sales'[active_skus] )

/* 5 — format Currency $ */
Avg Daily Sales Value =
AVERAGEX (
    VALUES ( 'dim_date'[date] ),
    [Total Sales Value]
)

Category

/* 6 */
Category Units =
SUM ( 'agg_daily_category_sales'[units_sold] )

/* 7 — format Currency $ */
Category Sales Value =
SUM ( 'agg_daily_category_sales'[sales_value] )

/* 8 */
Category Active Stores =
SUM ( 'agg_daily_category_sales'[active_stores] )

Product performance

/* 9 */
Product Units =
SUM ( 'agg_product_performance'[units_sold] )

/* 10 — format Currency $ */
Product Sales Value =
SUM ( 'agg_product_performance'[sales_value] )

/* 11 */
Product Avg Daily Units =
AVERAGE ( 'agg_product_performance'[avg_daily_units] )

/* 12 */
Product Demand Volatility =
AVERAGE ( 'agg_product_performance'[demand_volatility] )

/* 13 — format Currency $ */
Product Avg Price =
AVERAGE ( 'agg_product_performance'[avg_sell_price] )

/* 14 */
Stores Selling =
SUM ( 'agg_product_performance'[stores_selling] )

/* 15 */
Product Units Rank =
RANKX (
    ALLSELECTED ( 'agg_product_performance'[item_id] ),
    [Product Units],
    ,
    DESC,
    DENSE
)

Demand trends

/* 16 */
Daily Units =
SUM ( 'agg_demand_trends'[daily_units] )

/* 17 — format Currency $ */
Daily Sales Value (Demand) =
SUM ( 'agg_demand_trends'[daily_sales_value] )

/* 18 */
Rolling 7D Units =
SUM ( 'agg_demand_trends'[rolling_7d_units] )

/* 19 */
Rolling 28D Units =
SUM ( 'agg_demand_trends'[rolling_28d_units] )

/* 20 */
Rolling 7D Avg Units =
AVERAGE ( 'agg_demand_trends'[rolling_7d_avg_units] )

/* 21 */
Active SKUs (Demand) =
SUM ( 'agg_demand_trends'[active_skus] )

/* 22 */
Active Stores (Demand) =
SUM ( 'agg_demand_trends'[active_stores] )

Event & price

/* 23 */
Event Units =
SUM ( 'agg_event_sales'[units_sold] )

/* 24 — format Currency $ */
Event Sales Value =
SUM ( 'agg_event_sales'[sales_value] )

/* 25 — format Percentage */
Category Share =
AVERAGE ( 'agg_store_category_sales'[category_sales_share] )

/* 26 — format Currency $ */
Price Avg =
AVERAGE ( 'agg_price_analysis'[avg_sell_price] )

/* 27 — format Currency $ */
Price Median =
AVERAGE ( 'agg_price_analysis'[median_sell_price] )

Time intelligence

Requires dim_date[date] marked as the date table.

/* 28 — format Currency $ */
Sales Value Previous Year =
CALCULATE (
    [Total Sales Value],
    SAMEPERIODLASTYEAR ( 'dim_date'[date] )
)

/* 29 — format Percentage */
Sales Value YoY % =
VAR Curr = [Total Sales Value]
VAR Prev = [Sales Value Previous Year]
RETURN
IF (
    Prev = 0 || ISBLANK ( Prev ),
    BLANK (),
    DIVIDE ( Curr - Prev, Prev )
)

/* 30 */
Units Previous Year =
CALCULATE (
    [Total Units],
    SAMEPERIODLASTYEAR ( 'dim_date'[date] )
)

/* 31 — format Percentage */
Units YoY % =
VAR Curr = [Total Units]
VAR Prev = [Units Previous Year]
RETURN
IF (
    Prev = 0 || ISBLANK ( Prev ),
    BLANK (),
    DIVIDE ( Curr - Prev, Prev )
)

/* 32 — format Currency $ */
Sales Value MTD =
TOTALMTD ( [Total Sales Value], 'dim_date'[date] )

/* 33 — format Currency $ */
Sales Value YTD =
TOTALYTD ( [Total Sales Value], 'dim_date'[date] )

Quick index

Dashboard 1 screenshot — Executive (updated)

Power BI page Executive after UI fixes: sales card 191.58M, daily demand + rolling 7D, five KPI cards including 2016 YTD vs 2015 YTD ≈ 18%, years 2011–2016 selected.

1. Scope & method

Source tables: agg_daily_store_sales, agg_daily_category_sales, agg_demand_trends, dim_date. Facts were not required — Executive visuals are built from Gold aggregates (same design as the Power BI Import model).

2. Visualization & interaction (Dashboard 1 — Executive)

The Executive page is designed as a single-screen overview: KPI strip → demand trend → store and category breakdown. Interaction is driven by the year slicer, visual highlighting, and the star-schema filter path (dim_date / dim_store → aggregates → measures).

2.1 Page layout (information hierarchy)

┌─────────────────────────────────────────────────────────────┐
│  [ Year slicer ]                                            │
│  [ Total Units ] [ Sales $ ] [ YoY % ] [ Avg Daily Sales ]  │
│  [──────── Demand line: daily vs rolling 7D (wide) ───────] │
│  [ Store ranking bar (left) ] [ Category columns (right) ]  │
└─────────────────────────────────────────────────────────────┘

2.2 Visual-to-field map

2.3 Interaction design

2.4 Model rules that enable interaction

• Relationships are many-to-one (*:1) from aggregates to dimensions, cross-filter direction Single (dimension filters fact/agg — not the reverse by default).
• dim_date is marked as the date table so SAMEPERIODLASTYEAR / YoY cards respond correctly when the year slicer changes.
• Measures in _Measures recalculate in the current filter context — cards are not static numbers; they are interactive KPIs.

2.5 Suggested analyst click-path

1. Set year slicer to a full year (e.g. 2015) for clean totals.
2. Read the four KPI cards (size, growth, daily run-rate).
3. Scan the demand line for trend / seasonality (use day grain).
4. Click top store (CA_3) → see how category mix and KPIs shift.
5. Clear selection → click FOODS → see store ranking under that category.
6. Move to Store / Product / Demand pages with synced year for deeper detail.

2.6 Visualization caveats (interaction + interpretation)

• ! Multi-selecting all years (2011–2016) still mixes partial and full years for KPI totals and YoY % — read cards with that caveat.
• ✓ Demand X-axis is plain date (not Date Hierarchy), so daily seasonality stays visible.
• ! Cross-filtering between category and store works through shared date / measure paths; if a visual goes blank, check Edit interactions or missing relationships.
• ✓ Cards + ranked bar + category column is an appropriate executive pattern: summary → trend → contribution breakdown.

3. Key pattern findings

3.1 Incomplete years distort annual totals (critical)

Training sales run 2011-01-29 → 2016-05-22. 2011 has 337 days; 2016 has only 143 days (through 22 May). Full calendar years are 2012–2015 only.

Finding: Before the fix, a year-grain demand chart made 2016 look like a collapse. With the updated daily line, demand clearly trends upward and 2016 remains strong on a per-day basis (~41.8K avg daily units vs ~37.8K in 2015). Annual KPI totals can still look low for 2016 when all years are selected — that is partial-year coverage (143 days), not a demand crash. Prefer average daily metrics or same-calendar-window YoY (Jan–22 May 2016 vs Jan–22 May 2015: sales +18.0%).

3.2 Growth is real on comparable years

On full years 2012→2015, sales CAGR is about 9.1%. Year-over-year sales: 2013 +10.0%, 2014 +5.4%, 2015 +12.0%. Units grew more slowly than sales in later years (price / mix effects). The dashboard YoY card (~27%) reflects a DAX SAMEPERIODLASTYEAR over the full selected range (including asymmetric year edges), not a clean full-year 2015 vs 2014 compare.

3.3 Store concentration — CA_3 dominates

Ten M5 stores across CA / TX / WI. Ranking by sales value matches the dashboard bar chart exactly:

Finding: CA_3 sells ~2.6× CA_4. Top 3 stores ≈ 40% of sales. State mix: CA 44.9% · TX 28.8% · WI 26.4%. California is both the largest region and the home of the best and worst stores — store-level ops matter more than state label alone.

3.4 Category mix — FOODS is the business

FOODS $111.1M (58.0%), HOUSEHOLD $57.1M (29.8%), HOBBIES $23.3M (12.2%). FOODS share drifts down slightly across full years (≈60% in 2011–12 → ≈55% in 2015) as HOUSEHOLD/HOBBIES grow, but FOODS remains the primary revenue engine and the natural focus for demand-planning accuracy.

3.5 Calendar & seasonality

• Christmas: 25 Dec each year has near-zero demand (11–20 units network-wide) — stores effectively closed; keep this in any forecast baseline.
• Week: Sat–Sun highest (~41K avg daily units); Tue–Thu lowest (~30–30.5K). Weekend uplift ≈ +35% vs midweek.
• Month (2012–2015): softest in Jan; stronger Jun–Sep (summer peak for this FMCG panel).

4. Dashboard quality notes

• ✓ Total Sales Value card formatted (shows 191.58M, not raw long decimals).
• ✓ Demand line uses continuous date (daily units + rolling 7D avg). Seasonality and Christmas dips visible.
• ✓ Comparable YoY card added: Sales Value 2016 YTD vs 2015 YTD % shows 0.18 (18%) for Jan 1–May 22 vs same window prior year. Keep the all-years Sales Value YoY % (27.08%) only as a SAMEPERIODLASTYEAR illustration — prefer the YTD card for the growth story.
• ✓ Store ranking and category columns match Gold aggregates.

Optional polish: format the YTD card as Percentage with 1–2 decimals so it displays 18.0% instead of 0.18.

5. What a data-analysis project report should include

For this Walmart M5 platform project, a complete analysis write-up (course / portfolio / stakeholder pack) should cover the sections below — not only dashboard screenshots.

6. Recommendations (from Executive view)

1. Optional polish: format 2016 YTD vs 2015 YTD as Percentage (18.0%) instead of 0.18.
2. Caption the demand chart if needed: “2016 series ends 22 May (partial year)”.
3. Treat CA_3 as the benchmark store and CA_4 as a turnaround / assortment study case.
4. Weight demand-planning effort toward FOODS (58% sales) while monitoring HOUSEHOLD growth share.
5. Always exclude or specially model 25 Dec in forecasting baselines.
6. Re-run python scripts/analyze_executive_dashboard.py after each Gold refresh to confirm the dashboard still matches export.

7. Reproduce

python scripts/analyze_executive_dashboard.py

Inputs: data/gold_export/agg_*.parquet, dim_date.parquet. Dashboard screenshot: assets/executive-dashboard-page1.png (Figure 1 above). Power BI page Executive with years 2011–2016 selected.