The data for this this dashboard is visualised in powerbi showing the sales report for a Tech company.
Retail analysis has a trap in it: total sales tells you almost nothing on its own. A chain can grow revenue while every existing store declines, simply by opening new ones. So this report is built around comparison rather than totals, and every number on it is answering "compared to what".
Three comparisons run through the whole thing: this year against last year, each district against the others, and new stores against the established estate. The pages follow that order.
A classic retail star schema. The fact table sits in the middle and everything else describes a way of slicing it.
| Table | Grain and role |
|---|---|
| Sales | The fact table. Sales value and units, keyed to store, item and time. |
| Store | The store dimension, and the important one. Carries chain, selling area square footage and the opening date, which is what makes both Sales Per Sq Ft and the new-store analysis possible. |
| District | The management hierarchy above store, including district manager, so performance can be attributed to a person rather than a postcode. |
| Item | Product dimension for the category and unit-level analysis. |
| Time | Date dimension on a fiscal calendar rather than a calendar year, which matters in retail where the year rarely starts in January. |
| KPI | A small table holding the goal values that the variance measures compare against. |
Square footage is the quiet hero of this model. Without it, a big store always beats a small one and the comparison is meaningless. Sales Per Sq Ft normalises for size and is the only measure here that lets a 2,000 sq ft store and a 20,000 sq ft store be judged on the same terms.
Column names below are taken from the model. These are the measure definitions as the model computes them, not a paste of the formula bar.
The pairing that everything else is built on. Last year is a time-shifted version of the same base measure rather than a separate column, so it stays correct at every level of the fiscal hierarchy.
TotalSales = SUM( Sales[Sales] ) TotalUnits = SUM( Sales[Units] ) This Year Sales = CALCULATE( [TotalSales], Time[FiscalYear] = MAX( Time[FiscalYear] ) ) Last Year Sales = CALCULATE( [TotalSales], SAMEPERIODLASTYEAR( Time[Date] ) )
Absolute variance answers "by how much", the percentage answers "is that a lot". Both are needed: a large district can move a big absolute number on a small percentage, and a small district does the opposite.
Total Sales Var = [This Year Sales] - [Last Year Sales] Total Sales Var % = DIVIDE( [Total Sales Var], [Last Year Sales] )
Sales Per Sq Ft = DIVIDE( [This Year Sales], SUM( Store[SellingAreaSize] ) ) Avg $/Unit TY = DIVIDE( [This Year Sales], CALCULATE( [TotalUnits], Time[FiscalYear] = MAX( Time[FiscalYear] ) ) )
Average price per unit is the measure that separates a genuine sales increase from an inflation-driven one. Revenue up while units are flat and price per unit is up is a different story from revenue up because more people bought.
New Stores =
CALCULATE(
DISTINCTCOUNT( Store[StoreNumberName] ),
FILTER( Store, YEAR( Store[OpenDate] ) = MAX( Time[FiscalYear] ) )
)
KPI01 compares actual performance to the target held in the KPI table, which is what drives the gauge on the New Stores page.
KPI01 = DIVIDE( [This Year Sales], SUM( KPI[GoalValue] ) )
The headline page. This Year Sales as a card against last year, sales by chain as a pie, and the chart that does the real work: Total Sales Variance by Fiscal Month and District Manager, a combo chart putting variance bars against a variance percentage line. Attributing variance to a named manager rather than a region is a deliberate choice; it turns a chart into a conversation. A map and a scatter give the geographic and size-versus-performance views alongside.
The same question at district level and month granularity, with a bar chart for ranking and a scatter for spotting the outliers. This is where a district that is up overall but down in recent months becomes visible, which the annual view hides completely.
The most interesting page structurally. A waterfall chart breaking down what actually drove the year-on-year change, a gauge tracking performance against the KPI goal, a matrix for detail, and a pair of toggle buttons that switch the chart between a ribbon view and a combo view. Two visuals occupy the same space and the buttons swap them through bookmarks, which is how you fit two answers into one screen without shrinking either.
A supporting detail page for unit-level analysis, and an info page documenting the report for whoever inherits it. The Q&A button appears on every page, letting a user ask a question in natural language rather than hunting for a visual that answers it.
The measures answer "how are the stores performing" thoroughly, but they stop at the store. The obvious extension is to move down to the customer: cohort the customer base by first purchase month and track how much each cohort still spends three, six and twelve months later. That distinguishes a chain growing because it keeps its customers from one growing because it keeps replacing them, and it is the analysis this page is named for. The building blocks are already here, since the transaction grain and a date dimension are all a retention cohort needs. Beyond that, adding basket size and items per transaction would show whether growth comes from more visits or bigger baskets, which is a different lever for the business to pull.