What 600+ Store Reporting Taught Me About Power BI at Scale

Most businesses worry about getting data into one place. Multi-site retail has a different problem: the data is already in one place, there is just an enormous amount of it, and almost every question someone asks about it depends on which store, which region, which week and which comparison period they mean.

That was the environment I worked in at M&S, building and supporting national reporting across more than 600 stores.

The challenge

At that scale, the question is never "can we get a report." It is "can we get a report that a store manager, a regional lead and a head office director can all open at the same time and trust the same numbers in." Those three people are asking completely different questions of the same data. The store manager wants today. The regional lead wants this week versus last week across their patch. The director wants the national picture and where it is moving.

The temptation in most organisations is to build three different reports for three different audiences. That works for a while. Then someone in a meeting asks why the regional number does not match the store number, and trust in the whole system takes a hit it does not easily recover from.

What we built

The approach was to build one properly modelled dataset that could support every level of the business, rather than separate reports stitched together after the fact. Get the data model right once, and the same numbers roll up consistently whether you are looking at a single till in a single store or the whole estate.

That meant being disciplined about where logic lived. Calculations belonged in the model, not duplicated across different report pages with slightly different assumptions. It meant building in the filtering and hierarchy so a regional lead could drop into their region without needing a separate report built for them. And it meant performance mattered as much as accuracy - a dashboard that takes thirty seconds to refresh in a Monday morning meeting does not get used twice.

We also had to be realistic about refresh cycles. National retail reporting does not need to be real-time to be useful. It needs to be reliably daily, accurate, and available before the people who need it start their day.

The results

Leadership across the business worked from a single, consistent set of numbers, at every level from store to national. Questions that used to take a follow-up email and a day's wait - "can you check why this region looks different to last week" - became something people could answer themselves, in the dashboard, in seconds.

The bigger shift was trust. Once people stopped needing to double check the numbers against their own spreadsheet, the reporting actually started getting used to make decisions, rather than just being looked at and quietly verified elsewhere.

What this means for your organisation

If you run a multi-site business - retail, hospitality, leisure, anything with more than a handful of locations - the same pattern almost always shows up. Head office has a national view. Each site or region has its own version, often in a spreadsheet, often slightly different from head office's numbers. Nobody is lying about the data. The data just was not built to agree with itself in the first place.

That is a data modelling problem, not a dashboard problem, and it is very fixable. The investment is usually smaller than people expect, especially set against the time currently spent reconciling numbers between head office and the regions every single week.