The data architecture behind Restaurant Performance

Pretty dashboards are everywhere.

Trustworthy numbers that operators actually trust are not.

Behind the Restaurant Performance dashboard — the crop below is the same KPI language you see on the full board (net sales, covers, food cost %, labor cost %, channel mix, daypart heat, inventory urgency) — is a deliberate data architecture. The visual is the last mile. The model is what keeps every scorecard honest.

Open live dashboard ↗  ·  Work Showcase card

What the architecture is doing

• Star schema for restaurant operations — fact tables for sales, labor, and inventory movements; dimensions for Location, Concept, Daypart, Menu Item, Channel, and Date.
• Semantic model with consistent DAX — food cost %, labor %, table turns, and contribution margin mean the same thing on every page and every location.
• Automated pipelines from systems that run the business — POS, labor/scheduling, inventory, online ordering — so the board is current every morning without manual exports or 2 a.m. CSV drops.

Below is a DAX starter you can paste into a model that follows that layout. It is intentionally incomplete: core math and naming patterns, not every measure, relationship, or RLS rule from a production deployment. Use it as a working foundation — then extend for your POS grain, comps, and labor rules.

How the data needs to be laid out

These measures assume a classic star with single-direction relationships from facts to dimensions. Grain matters: get this wrong and every % will lie under filters.

• FactSales — one row per sales line (or check line) after voids/comps policy is applied. Required columns: DateKey, LocationKey, ChannelKey, DaypartKey, MenuItemKey (optional for menu engineering), NetSalesAmount (currency), CoverCount (whole covers allocated to the line or check — pick one grain and stick to it), FoodCostAmount (recipe/theoretical or actual COGS attributed to the sale).
• FactLabor — one row per labor punch/shift slice. Required: DateKey, LocationKey, DaypartKey (if you allocate hours to daypart), LaborCostAmount, LaborHours.
• FactInventoryMove (for urgency tiles) — receipts, transfers, waste, sales depletion. Required: DateKey, LocationKey, ItemKey, QtyOnHand or movement qty + a daily on-hand snapshot process.
• DimDate — contiguous calendar with DateKey, Date, WeekdayName, WeekdaySort, IsMTD helper columns as needed.
• DimLocation — LocationKey, LocationName, ConceptKey (or Concept as attribute).
• DimChannel — Dine-in, Takeout, Delivery, Catering (match your POS tender/channel map).
• DimDaypart — Lunch, Happy hour, Dinner, Late (business-defined time bands, not POS free text).
• DimMenuItem / DimInventoryItem — stable item keys; never join on names alone.

Relationships (examples): FactSales[DateKey] → DimDate[DateKey], same pattern for Location, Channel, Daypart. Mark DimDate as the date table. Keep fact-to-fact relationships out of the model unless you have a deliberate bridge design.

DAX starter — core measures

Create a measures table (e.g. _Measures) and paste. Names match the crop: net sales, covers, food cost %, labor cost %.

// --- Core sales & guest counts ---
// Assumes FactSales grain is stable and NetSalesAmount is post-discount, post-void.

Net Sales :=
SUM ( FactSales[NetSalesAmount] )

Covers Served :=
SUM ( FactSales[CoverCount] )

// Average ticket — protect divide-by-zero when a filter returns no covers
Avg Ticket :=
DIVIDE ( [Net Sales], [Covers Served] )

// Prior MTD sales for the "vs prior MTD" sparkline / variance tile
// Requires DimDate with a proper date column marked as date table
Net Sales Prior MTD :=
CALCULATE (
    [Net Sales],
    DATEADD ( DimDate[Date], -1, MONTH )
)

Net Sales vs Prior MTD % :=
DIVIDE (
    [Net Sales] - [Net Sales Prior MTD],
    [Net Sales Prior MTD]
)

// --- Cost ratios (same definition on every page / location) ---
// FoodCostAmount on FactSales = cost attributed to sold items (recipe or actual).
// Labor from FactLabor so labor can be filtered by daypart independently of sales lines.

Food Cost :=
SUM ( FactSales[FoodCostAmount] )

Food Cost % :=
DIVIDE ( [Food Cost], [Net Sales] )

Labor Cost :=
SUM ( FactLabor[LaborCostAmount] )

Labor Cost % :=
DIVIDE ( [Labor Cost], [Net Sales] )

// Optional: plan variance tiles ("vs plan 29.0") — store targets on DimLocation or a small FactPlan table
// Food Cost % Plan := SELECTEDVALUE ( DimLocation[FoodCostPlanPct] )
// Food Cost % vs Plan pts := [Food Cost %] - [Food Cost % Plan]

// --- Sales by channel ---
// Use DimChannel on the legend; measure stays simple so the donut and $ labels always reconcile to [Net Sales].

Net Sales by Channel :=
[Net Sales]

// Share of selected net sales (works with location / date slicers)
Channel Mix % :=
DIVIDE (
    [Net Sales],
    CALCULATE ( [Net Sales], ALLSELECTED ( DimChannel ) )
)

// Example channel labels in DimChannel[ChannelName]:
// Dine-in | Takeout | Delivery | Catering

// --- Daypart x weekday heat ---
// Matrix: rows = DimDaypart[DaypartName] (sorted by DaypartSort)
//          columns = DimDate[WeekdayName] (sorted by WeekdaySort)
// Values = this measure (sales or covers — pick one and document it)

Heat Value - Net Sales :=
[Net Sales]

// If you prefer covers in the heat cells:
// Heat Value - Covers := [Covers Served]

// Conditional formatting on the matrix does the color scale;
// keep the measure additive so Lunch/Mon + Lunch/Tue ... still sums cleanly.

// --- Inventory urgency tiles (simplified starter) ---
// Production models often use a daily on-hand snapshot + 7/14/28-day usage.
// This pattern assumes:
//   FactInventoryDaily[DateKey], [LocationKey], [ItemKey], [QtyOnHand]
//   FactSales or depletion feed for usage (here: FactSales[QtySold] at item grain)
//
// Extend with transfers, waste, and unit-of-measure conversion for real kitchens.

Qty On Hand :=
SUM ( FactInventoryDaily[QtyOnHand] )

// Average daily units sold over the last 14 days (filter context: item + location)
Avg Daily Sales Units 14d :=
AVERAGEX (
    DATESINPERIOD ( DimDate[Date], MAX ( DimDate[Date] ), -14, DAY ),
    CALCULATE ( SUM ( FactSales[QtySold] ) )
)

Days On Hand :=
DIVIDE ( [Qty On Hand], [Avg Daily Sales Units 14d] )

// Urgency bands for tile color (example thresholds — tune per concept)
// Critical < 2d | Watch 2–4d | Healthy > 4d
Inventory Urgency Band :=
VAR d = [Days On Hand]
RETURN
    SWITCH (
        TRUE (),
        ISBLANK ( d ), "No usage",
        d < 2, "Critical",
        d < 4, "Watch",
        "Healthy"
    )

What this starter deliberately leaves out

On purpose — so the post stays useful without pretending one paste replaces a full build:

• Full relationship diagram, role-playing dates, and bi-directional pitfalls
• Comps, voids, employee meals, and gift-card liability rules unique to each POS
• Tip pooling, OT premiums, and salaried allocation into daypart labor
• Recipe versioning, yield variance, and multi-UOM inventory conversions
• Row-level security by region/franchisee and workspace deployment patterns

Those are where restaurant models usually get customized. The starter above is the shared skeleton: same measure names, same grain discipline, same “one definition of food cost %” everywhere on the board.

See the full board

Open the live Restaurant Performance sample in Power BI Service — same multi-location board this article is built around:

Open live dashboard ↗  ·  Work Showcase card

Related industry boards also live on the Work Showcase. Analytics projects start from $1,200 — see Data Analytics or open a project inquiry when you want this modeled on your POS, labor, and inventory stack.