Dorcas_Akinnibosun.xlsxOpen to work · Lagos · Remote, home page
Power BI

Modelling $55.8M of sales in Power BI with a star schema

How five raw tables became a four-page Power BI report for a global electronics retailer: the star schema, the DAX comparisons and what the model revealed.

Dorcas Akinnibosun2 min read

Maven Electronics is a multinational electronics retailer with several years of transactions across products, customers, regions, sales channels and stores. The goal was to find patterns in revenue, spot the strongest products and regions, understand who buys, and compare store performance.

The finished report is live on this site. This post is about what happens before the first chart: the model.

Five raw tables

The data arrived as five separate tables: sales, customers, stores, products and exchange rates. Together they cover 26.3K orders, 15.3K customers and 67 stores.

Raw files are never ready to chart. I cleaned and shaped each table in Power Query first, so problems are fixed once, at the source, rather than patched inside individual visuals.

Why a star schema

I modelled the tables as a star schema: the sales table in the middle, holding the transactions, with customers, stores and products around it as lookup tables.

That shape does three things for a report like this:

  • Filters behave predictably. Picking a store, a product category or a customer group filters the sales through one clear path.
  • Measures stay simple. Totals, averages and comparisons are written once and work on every page and every filter.
  • It scales. Adding a page or a slicer doesn't mean rebuilding relationships.

Time intelligence

Most business questions are comparisons: this year against last year, year to date, the top performers this period. I used DAX time-intelligence measures (year-on-year, year-to-date, prior year and Top N) so every KPI card shows its comparison automatically, whatever the reader filters to.

That is how the overview can say revenue reached $55.8M, up 12.1% year on year, and still give the right comparison when you filter to a single region.

What the model made visible

With the model in place, the patterns were quick to find:

  • Revenue peaked in 2019 and declined from 2020, while the average unit price held steady at $356.8.
  • Thursdays and Saturdays drive orders, with Saturday the single busiest day.
  • Loyal customers are 84.6% of the base. That is strong retention, but also a sign the business may be saturating.
  • The United States earns $23.8M; France brings in $1.2M.
  • Physical stores generate 79.55% of revenue and online only 20.45%, which leaves room to grow the online channel.

Each of those became a recommendation in the case study: promotions on peak days, a better online checkout, localised marketing for weaker regions, tiered loyalty rewards and stock planning for the winning categories.

The takeaway

The visuals get the attention, but the model decides whether they can be trusted. A clean star schema is why this report can answer a new question in a click instead of a rebuild.

More

Keep reading.

All posts →
Contact

=IF(you_have_data, "let's look at it together", "say hello anyway")

Whether it's a role, a messy spreadsheet or a question about one of the case studies, messages come straight to my inbox.

Open to data analyst and BI work: full-time, contract or freelance, remote or on-site