Kosta Cálida

01 · The starting point

A store network generates data all the time. The challenge is turning it into context.

A store network can pile up thousands of receipts and have plenty of data available, but that doesn’t mean the information is easy to use. The sales data already existed: the problem was how to use it.

There was enough information to know the results, but no structure that made it easy to analyse what lay behind each change.

In this project I started from point-of-sale data and built a Power BI reporting system designed to go from an overview of the business to the detail that explains each result. I framed the project around a simple question: what should someone in leadership be able to answer when they notice something has changed?

My aim was for a variance in a KPI not to end in a number, but to be traceable until you find the store, product or receipt behind it. From there I built the model, the measures and the report pages so the analysis could move from the summary down to the detail.

02 · The goal

From a variance in a KPI to the receipt that explains it.

A KPI can tell me that sales have dropped, but that’s only the start of the analysis. I wanted the report to let you follow that variance and understand where it happens: which store is behind it, which products are driving it and what happened at receipt level.

That’s why I designed the reporting with different levels of detail. The idea is simple: start with a signal and be able to dig down until you find the context that explains it.

03 · Data model

A structure designed to analyse sales from different angles.

Before building the charts, I needed to put the data in order. The idea was that the same sale could be related to its store, product, date and budget without duplicating information or complicating the analysis.

To do that I built a star schema, a way of organising data that separates the sales and budget information from the data we use to analyse it, such as stores, products or dates.

The model starts from two main tables:

• Ventas → what was sold, when, where and for how much.
• Presupuestos → which sales were planned.

And I related them to three tables that add context:

• Fecha → day, month, quarter and year.
• Productos → product and category.
• Tienda → store, location, floor area, employees, rent and population.

That way I can move from a general question, such as "how much have we sold?", to more specific ones such as "which store is behind the result?" or "which product is driving it?", without having to rebuild the data for each analysis.

I also added some helper tables to control elements of the dashboard, such as the store, product and category selectors and the Top N rankings. These tables don’t contain new sales: they let users interact with the report without changing the main structure of the model.

The aim wasn’t to build a complex model for its own sake, but to have an orderly base that makes it possible to ask questions about the business and reach the detail needed to answer them.

04 · DAX

92 measures, organised like code.

As the model grew, so did the number of measures needed to analyse it. I reached 92 DAX measures and decided to organise them in a clear structure, grouping the logic so each calculation would be easier to find, maintain and reuse — the way you organise a code project, not a spreadsheet.

It wasn’t just about getting the measures to work. I also wanted to be able to come back to the project after a while and quickly understand where each calculation was and what it did.

05 · Power BI

From raw point-of-sale data to a dashboard with drill-down.

The next step was to take the model into Power BI and build navigation that goes from the general to the specific. I started from the point-of-sale data and built different levels of analysis so users could dig deeper when they found a relevant change. Drill-down became a key part of the report: not just showing the figure, but letting you investigate what lies behind it.

06 · Users

Thirteen pages, four different roles.

Not everyone needs to look at the information in the same way. That’s why I structured the report’s 13 pages around different analysis needs and user profiles. The information changes with the level of detail each person needs, but the model and the analysis logic stay connected. That way the report works as a reporting system, not just a collection of separate dashboards.

08 · Report pages

Two of the report’s thirteen pages.

These are two examples of how I took the model and the analysis logic into Power BI. Each page answers a different need, but they all start from the same data structure and share a common navigation logic. The aim was to avoid isolated dashboards and build a system where the different views complement each other.

09 · What it makes possible

What the system lets you see, in a specific case.

The report proves its worth when it’s used to answer specific questions. For example, if a KPI shows a change, I can start with the overall result, compare stores or categories and dig down until I find what’s causing the change.

The year closed at €717,304 against a budget of €776,000: a variance of −€58,700 (−7.56%). The report’s cover breaks that figure down by store: Mahón, the network’s top seller (€277,000), was also the one dragging the variance down the most (−€47,700). Sant Francesc Xavier and Ibiza (town), with less volume, were the only two in positive territory (+€6,000 and +€4,600).

A similar pattern appeared by category: Drinks and soft drinks led in both sales and margin (+37.58%), while Convenience and Express Store closed with a negative margin despite similar volume.

For me, this is one of the most interesting parts of Power BI: connecting different levels of information so users don’t have to start from scratch every time a new question comes up.

Tools

Tech stack

10 · Conclusion

Order matters.

Working on this project made me pay much more attention to what happens before building a chart. When the data is well structured, the measures are organised and the model has a clear logic, it’s much easier to build a report that can grow without turning into a set of pieces that are hard to maintain.

It also helped me understand something I think matters in Power BI: a good dashboard doesn’t end when we find the right KPI. The interesting part starts when we can ask what lies behind that KPI and have a model ready to find the answer.