Supermarket Price Optimization Dashboard

Leveraged Power BI to analyze multi-store pricing data, identifying a 5.16% savings strategy on monthly household spending.

  • Microsoft Excel
  • Microsoft PowerBi
  • DAX
  • Data Modeling

At a Glance

  • Identified KSh 743 (5.2%) potential savings from a KSh 14,408 grocery bill by optimizing store selection.
  • Recommended mix-and-match purchasing with focus on 5 key items driving 78% of savings.
  • Built an interactive Power BI dashboard to compare store and product-level costs.

Dashboard

Dashboard built in Power BI to compare product-level costs and simulate savings.

Business Problem

Like most people, I do a big monthly shop for household essentials. My budget is tight, and I’ve noticed my bill creeping up. I usually just shop at the nearest supermarket (Naivas) out of habit, but I also have Quickmart and Carrefour nearby. I realized I might be overpaying simply by not comparing prices.

My goal was to use my own shopping list data to answer:
“Which supermarket, or combination of supermarkets, will give me the lowest total bill for my exact monthly needs?”

Approach & Analysis

Step 1: Data Collection

  • Extracted item-level prices from the online stores of Naivas, Quickmart, and Carrefour.

  • Compiled data in Excel with columns: Supermarket, Product Details, Product, Product Type (Food / Cleaning / Toiletries), Quantity, Cost/Pkt, Total Cost.

Step 2: Data Preparation in Power BI

  • Imported dataset into Power BI.

  • Cleaned for consistency: corrected data types, spelling, and capitalization.

Step 3: Modeling & DAX Measures

Built measures to calculate total spend by supermarket and potential savings:

  • Total Bill = SUMX(Quantity × Cost/Pkt)
  • Naivas Bill / Quickmart Bill / Carrefour Bill = CALCULATE([Total Bill], filter by supermarket)
  • Optimal Mix Total = SUMX per product of [Min Product Cost]
  • Potential Savings = [Naivas Bill] – [Optimal Mix Total]
  • Potential Savings % = DIVIDE([Potential Savings],[Naivas Bill],0)
  • Cheapest Store helper logic to dynamically return the best price source per product.

Step 4: Visualization & Insights

Designed a Power BI dashboard with:

  • KPI Cards: Bills per supermarket, Optimal Mix Total, Potential Savings (Amount and %).
  • Pie Chart: “Where my money goes” (optimized basket).
  • Stacked Bar Chart: Supermarket performance by product type.
  • Bar Chart: Top 5 “biggest win” items with the highest price gaps.
  • Table: Recommended shopping list → Product | Min Cost | Cheapest Store | Product Type.
  • Text Box: Actionable recommendations.

Key Findings

  • If I buy everything at Naivas (my usual habit), I pay KSh. 14,408.
  • Switching entirely to the cheapest single supermarket (Carrefour) saves Ksh. 470 (3.3%).
  • Mixing and matching products across stores saves KSh. 743 (5.16%) compared to Naivas.
  • Top 5 items alone account for 77.65% of total savings.

Recommendations for an Optimal Shopping Strategy

  1. Prioritize Carrefour for single-store shopping. It offers a modest 3.3% savings over Naivas without needing to shop around.
  2. Adopt a mixed-store approach for maximum savings. Combining stores reduces the total bill by 5.16%, the best overall result.
  3. Focus on just the top 5 high-impact items. Switching stores for these products alone captures 77.65% of all potential savings, minimizing effort while keeping most of the benefit.
  4. Track supermarket price trends regularly. Small changes could shift which store is cheapest over time.
  5. Use this analysis to build an automated shopping list, ensuring the strategy stays updated without manual rework.
Scroll to Top