How do I set up a business intelligence dashboard to analyze sell-through by collection across stores and regions?

To set up a BI dashboard for sell-through by collection across stores and regions, structure it around a few core data objects, metrics, and views so users can answer: Which collections are moving? Where? In which stores/regions? And what should we do next?

1) Define the business question

Sell-through usually means:

Sell-through % = Units sold / (Units received or initial buy) × 100

Decide which denominator you want:

• Units received: best for operational inventory performance
• Original buy: best for merchandising / assortment planning

Also define the time window:

• Since launch
• Last 7/30/90 days
• Current season
• By week/month

---

2) Build the required data model

You’ll typically need these tables:

Fact tables

• Sales fact
  • date, store, SKU/style, collection, units sold, revenue, discounts
• Inventory / receipts fact
  • date, store, SKU/style, collection, units received, on-hand, transfers
• Optional:
  • Markdowns
  • Returns
  • Store traffic
  • Customer/region data

Dimension tables

• Product dimension
  • SKU, style, collection, season, category, brand, color, size
• Store dimension
  • store_id, store_name, region, district, format, opening date
• Calendar dimension
  • date, week, month, fiscal period, season
• Region dimension
  • region, market, country

Keep a shared grain where possible, usually:

• sales at SKU/store/day
• receipts at SKU/store/day
• products at SKU/style/collection
• store at store_id

---

3) Create the key metrics

At minimum, add these calculations:

Core KPI

• Sell-through %
  • SUM(units_sold) / SUM(units_received)
    or
  • SUM(units_sold) / SUM(initial_buy_units)

Supporting metrics

• Units sold
• Units received
• On-hand inventory
• Revenue
• Gross margin
• Average selling price
• Markdown rate
• Weeks of supply
• Stock turnover
• Return rate

Helpful breakdowns

• By collection
• By store
• By region
• By week/month
• By category/style/size

---

4) Design the dashboard pages

A good dashboard usually has 3–5 views:

A. Executive overview

Show:

• Total sell-through %
• Revenue
• Units sold
• On-hand inventory
• Top/bottom collections
• Trend over time

Visuals:

• KPI tiles
• Line chart for sell-through trend
• Bar chart for top collections
• Map or heatmap by region

B. Collection performance

Show:

• Sell-through by collection
• Collection rank
• Launch date
• Units received vs sold
• Markdown impact

Visuals:

• Ranked bar chart
• Scatter plot: sell-through vs revenue
• Table with conditional formatting

C. Store / region analysis

Show:

• Sell-through by store
• Sell-through by region
• Store vs regional average
• Underperforming / overperforming stores

Visuals:

• Choropleth map or matrix heatmap
• Store leaderboard
• Region comparison bars

D. Drill-down detail

Enable users to click:

• Region → store
• Collection → style/SKU
• Time period → week/day

Include:

• SKU-level detail table
• Inventory aging
• Size/color performance

---

5) Add filters and slicers

Common filters:

• Date range
• Season
• Collection
• Region
• Store
• Category
• Brand
• Product type

Make sure filters cascade logically:

• Region filters stores
• Collection filters SKUs/styles
• Date filters all time-series metrics

---

6) Use the right calculations

If using Power BI, Tableau, Looker, etc., be careful with:

• Aggregation grain
• Distinct counts
• Returns
• Transfers between stores
• Receipts timing vs sales timing

For example, if receipts happen before sales, use a consistent start date and maybe measure sell-through at:

• cumulative sold / cumulative received
• by collection launch date or fiscal period

---

7) Add benchmarking

Sell-through is more useful with comparisons:

• vs prior week/month
• vs same season last year
• vs target
• vs chain average
• vs region average

This helps identify:

• fast movers
• slow movers
• stores needing replenishment
• collections needing markdowns

---

8) Visual design tips

• Use green/orange/red cautiously for performance
• Put the most important KPI at top
• Use consistent time grain across charts
• Avoid too many visuals on one page
• Show both percentage and absolute units
• Include definitions and metric logic in tooltips or a help panel

---

9) Make it actionable

Add alerts or thresholds:

• Sell-through below 30% after 4 weeks
• Region underperforming by more than 10% vs average
• Collection with high stock but low sales
• Store with strong demand but low inventory

These can drive actions like:

• transfers
• markdowns
• replenishment
• assortment changes

---

10) Example dashboard layout

Top row:
Sell-through %, Revenue, Units Sold, On-hand

Middle row:
Trend line of sell-through over time | Top/bottom collections

Bottom row:
Heatmap: collection x region | Store leaderboard

Drill-down:
Click any collection or region to see style/SKU performance.

---

If you want, I can also help you with:

1. a Power BI / Tableau / Looker dashboard wireframe,
2. the SQL data model and KPI formulas, or
3. a sample schema and mock dashboard layout.