Strategic Coffee Shop Sales Performance Dashboard

Initial data profiling was conducted during development. The dashboard assumes a good standard of source data quality; however, robust error handling in DAX measures can mitigate minor inconsistencies.

• Total Sales:
  Total Sales = SUM(Transactions[Sales])
• Date Table:
  Date Table = CALENDAR(MIN(Transactions[transaction_date]), MAX(Transactions[transaction_date]))
• MoM Growth & Diff Sales:
  MOM Growth & Diff Sales = VAR month_diff = [CM Sales] - [PM Sales] VAR mom = month_diff / [PM Sales] VAR _sign = IF(month_diff > 0, "+", "") VAR _sign_trend = IF(month_diff > 0, "▲", "▼") RETURN _sign_trend & " " & _sign & FORMAT(mom, "#0.0%") & " | " & _sign & FORMAT(month_diff / 1000, "0.0k") & " vs LM"
• new MoM Label:
  new MoM Label = VAR month_diff = [CM Sales] - [PM Sales] VAR mom = month_diff / [PM Sales] VAR _sign = IF(month_diff > 0, "+", "") VAR _sign_trend = IF(month_diff > 0, "▲", "▼") RETURN _sign_trend & " " & _sign & FORMAT(mom, "#0.0%")

Purpose: Provides a single control to filter all visuals by a Month–Year selector, enabling viewers to focus on any period of interest.

• Source Column: Month Year (e.g., “Jun 2025”)
• Control Type: Dropdown or slicer with search enabled.
• Behavior:
  • Selecting a month instantly updates every chart, KPI card, and matrix across the dashboard.
  • Supports multi-select for comparative analysis across multiple months.

Purpose: Granular day-by-day view of sales to spot daily patterns at a glance.

• Key Setup:
• Date-Table Columns:
  • DayName = FORMAT('Date'[Date], "DDD")
  • WeekNumber = WEEKNUM('Date'[Date], 2)
  • DayNumber = FORMAT('Date'[Date], "D")
  • WeekdaySortKey = WEEKDAY('Date'[Date], 2)
• Matrix Heat-Map:
  • Rows: WeekNumber
  • Columns: DayName (sorted by WeekdaySortKey)
  • Values: DayNumber (for calendar grid) with conditional background by Total Sales (gradient from low→high)
• Tooltips: Tooltip page built with cards showing “Date”, “Total Sales”, “Total Orders”, “Total Quantity Sold” plus MoM change measure. Assigned as the matrix’s tooltip so hovering any cell shows all three KPIs.

Purpose: Immediate, high-level overview of critical business health metrics.

Examples: Total Sales, Total Quantity Sold, Total Orders.

Features:

• KPI Cards with MoM Comparison:
  • MTD Value vs. Last Month MTD via CM Sales & PM Sales measures
  • MoM Indicator (▲/▼ + % + Δ in “k”) using a single formatted measure
• Daily Trend-Line Charts:
  • One mini-chart per KPI showing day-by-day values
  • Dynamic chart titles displaying current value + MoM % change

Purpose: Visualize daily sales fluctuations and quickly identify days performing above or below average.

Chart Setup:

• Type: Column chart
• X-Axis: transaction_date
• Y-Axis: Total Sales

Key Features:

• Constant Line – Daily Average:
  Daily Avg Sales = AVERAGEX( ALLSELECTED(Transactions[transaction_date]), [Total Sales] )
  Plotted as a constant horizontal line for quick baseline comparison.
• Conditional Bar Coloring:
  Colour For Bars = IF( [Total Sales] > [Daily Avg Sales], "Above Average", "Below Average" )
  Applied as a rule in the chart’s color settings so bars above the average line stand out from those below.

Purpose: Reveal recurring sales peaks and troughs across days of the week and hours of the day for staffing and promotion planning.

Visual: Matrix Heat-Map

• Rows: Transaction Hour (R)
• Columns: Weekday Name (Mon–Sun)
• Values: Total Sales, colored via a gradient scale (e.g. darkest for highest, lightest for lowest)

Key Features:

• Gradient Color-Scale: Conditional formatting on the matrix cells (e.g. #6C260E → #D98D6C) to make high-volume slots pop.
• Custom Tooltips: On hover, show a mini tooltip page containing:
  • R (hour)
  • Weekday
  • Total Sales
  • Total Orders
  • Total Quantity Sold
• Drill-through Bar Charts:
  • Horizontal Bar by Hour: Sorted by sales volume, gradient-colored, no axes or grid clutter.
  • Vertical Bar by Weekday: Same styling, with data labels hidden behind matching font color for a clean look.

Purpose: Compare and benchmark sales across different locations.

Visual: Clustered bar chart

• Custom Location Labels:
  Label for Store Location = SELECTEDVALUE(Transactions[store_location]) & " | " & FORMAT([Total Sales]/1000, "$0.00k")
  (e.g. “Hell’s Kitchen | $40.30k” above each bar via a zero-value “PlaceHolder” series.)
• MoM Growth & Diff Sales: Reuse the “MOM Growth & Diff Sales” logic (▲/▼ + % + Δ) as the bar’s secondary data label. (e.g. ▲+21.7% | +7.2k vs LM)

Purpose: Reveal top revenue drivers for stocking and marketing focus.

Visuals: Two clustered bar charts (by Product Type and by Product Category)

• Custom Labels:
  • Product Type:
    SELECTEDVALUE(...) & " | " & FORMAT([Total Sales]/1000, "$0.00k")
  • Product Category: similar measure
  • MoM % Change:
    new MoM Label = VAR diff = [CM Sales] - [PM Sales] VAR mom = diff / [PM Sales] RETURN IF(diff > 0, "▲ ", "▼ ") & FORMAT(mom, "#0.0%")
  • Placeholder Series: Zero-value bar to host these labels above each bar.

Purpose: Compare sales share on weekdays vs. weekends to tailor staffing and promotions.

Typical Visual: Donut chart

Key Features:

• Weekday/Weekend Column (in Date table):
  Weekday / Weekend = IF( 'Date Table'[DayName] = "Sat" || 'Date Table'[DayName] = "Sun", "Weekend", "Weekday" )
• Donut Setup:
  • Legend: Weekday/Weekend
  • Values: Total Sales
• Formatting:
  • Title: “Sales by Weekday / Weekend”
  • Detail Labels: Category name + percentage
  • Slice Colors:
    • Weekday ⇒ #VA6239
    • Weekend ⇒ #95441F
  • Center Card: Total Sales card overlaid in the donut’s hole

• Canvas Size: 1400 px wide × 850 px high
• Shape: “E” layout—three horizontal bars plus left spine
• Colors:
  • Background/spine: #402B1E
  • Charts/dividers & highlights: #B36541 & #73361C

1. Top Bar (KPIs):
   • Four cards (Total Sales, Total Quantity Sold, Total Orders, and a Sales sparkline showing daily changes + MoM % change as title)
2. Middle Bar (MoM Trends):
   • Sales trend over the period (e.g., line chart with daily values and MoM annotation)
3. Bottom Bar (Details):
   • Three visuals side-by-side:
     • Store-level MoM growth bar
     • Sales by product
     • Sales by product category
     • Sales by days/hours
4. Left Spine (Filters & Nav):
   • Collapsible pane containing: date slicer, sales calendar, sales by weekend/weekday, sales by store location

• Cross-filtering between visuals
• Dynamic titles & annotations
• Contextual tooltips with drill-through capability

Sales consistently surge between 7 AM and 10 AM on Saturdays and Sundays, making weekend brunch the café’s single highest-volume period. This pattern suggests that targeted “brunch specials”—such as limited‐time food bundles or drink pairings—could capitalize on the existing foot traffic surge and further increase average ticket value during these critical hours.

Over the last six months, Coffee has maintained its leading share of total category revenue, followed by Tea in a distant but stable second. Together, these two categories contribute more than three‐quarters of all sales, with Bakery and Drinking Chocolate relegated to distant third and fourth. Because the Coffee vs. Tea ranking never wavers, strategic efforts (promotions, menu innovation, inventory prioritization) should focus on deeper differentiation within these two heavy‐hitting categories rather than attempting to elevate underperforming categories.

At the individual‐item level, Barista Espresso occupies the top spot, closely trailed by Brewed Chai Tea and Hot Chocolate; Gourmet Brewed Coffee rounds out the top four. The fact that these four items remain solidly in front—while the next‐ranked products sit markedly lower—confirms that classic espresso and tea offerings command the bulk of purchase decisions. Promotional calendars should therefore lean on these four best sellers (e.g., “Espresso Happy Hour” or “Chai Tea Latte Mondays”) to maximize incremental sales without diluting marketing spend across lower‐volume SKUs.

From January through May 2023, the top six products held their exact positions month over month. In June, two swaps occurred: Gourmet Brewed Coffee moved from fourth to third (overtaking Hot Chocolate), and Brewed Herbal Tea ascended from sixth to fifth (ahead of Brewed Black Tea). These June shifts—though isolated—signal that customer interest in premium/seasonal variants can quickly alter long‐standing ranking patterns. Monitoring promotional activity or seasonal menu changes around late spring may help explain these rank inversions and guide future assortment decisions.

Hell’s Kitchen has been No. 1 in sales for all six months, never relinquishing its crown despite narrowing margins against Austria and Lower Manhattan (which periodically exchanged second and third place). This enduring top‐spot performance underscores Hell’s Kitchen’s strong local loyalty. By contrast, the flux between Austria and Lower Manhattan indicates that those locations—while close in revenue—have not established the same sustained customer base. Any expansion of marketing spend or localized promotions should protect Hell’s Kitchen’s position (e.g., loyalty‐member perks) while aiming to stabilize revenue for the mid-ranked locations.

Monthly sales volumes reveal a textbook enterprise life-cycle over the January–June 2023 window:

• Launch (Jan–Feb): Modest, incremental revenue growth as awareness builds.
• Growth (Mar–May): Significant month-over-month gains indicate accelerating customer adoption, likely driven by positive word-of-mouth or effective springtime promotions.
• Shakeout (Jun): A slight revenue dip in June suggests increased market saturation or competitive pressure. To counteract this June slowdown and reignite growth, initiatives such as a mid-summer menu refresh, limited-edition beverage rollouts, or loyalty incentives could prompt another upward curve.

Across every month (except February’s 28-day cycle), sales fall below average during both the first seven days and the final three days. This symmetric pattern aligns with typical pay-cycle behavior: customers exhaust budgets at month-end then reallocate spending early in the following month. To smooth revenue, strategic “first-week” promotions—such as a small discount on brewed coffee—or “last-week” incentives (e.g., buy-one-get-one on pastry items) could mitigate the mid-month bulge and prevent abrupt troughs at period boundaries.