The data for this this dashboard is visualised in powerbi showing the sales report for a shoe manufacturer. There are are 3 dashboards which consist of:
1. The homepage showing high level sales in USD at YTD and consumer rating for the top products. The homepage also acts as a navigation to the sales and rating dashboard.
2. The Sales dashboard shows a sales trend by country and product category with sales for top 10 and bottom 10 by product and manufacturer.
3. This is consumer rating by product and category with a distribution map in place to analyse where rating are coming from. The map also acts as a filter by state to further drill into each metric on the dashboard.
The live version of the dashboard can be used below:
The data model from the survey consist of the main table which contain the sales data information. The other tables connected to the main table consist of Geography, Product, Sentiment and a date table used to filter out certain time frames.
Most sales reports stop at revenue. This one pairs sales with customer ratings, which changes the questions it can answer: not just which products sell, but whether the ones that sell are the ones people actually like. A product with high revenue and a falling score is a different problem from one with low revenue and a rising score, and only a model carrying both can tell them apart.
Three pages, in the order a review runs: a Homepage summary, a Sales page for the commercial detail, and a Ratings page for the sentiment view.
Two fact tables sharing a common set of dimensions, which is what makes the sales-versus-sentiment comparison possible without merging two different grains into one table.
| Table | Grain and role |
|---|---|
| fSales | Transactional sales: revenue and units, keyed to product, geography and date. |
| fSentiment | Customer ratings, carrying Score, at the same product and date grain so it can be filtered by the same slicers. |
| dProduct | ProductName, Category, Manufacturer, Segment. |
| dGeography | Country, Region, State, City, driving the map and the geographic drill. |
| dDate | Marked date table with Month and MonthName, driving all year-to-date and prior-year logic. |
Two facts, shared dimensions. Because both fact tables filter through the same product, geography and date dimensions, a single slicer changes both the revenue chart and the ratings chart at once. That is the whole design, and it is why the comparison holds together.
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 backbone of the report. Everything else is a variation on this pair, computed for revenue and for units.
Revenue YTD = TOTALYTD( SUM( fSales[Revenue] ), dDate[Date] ) Revenue YTD PRY = CALCULATE( [Revenue YTD], SAMEPERIODLASTYEAR( dDate[Date] ) ) Units Sold YTD = TOTALYTD( SUM( fSales[Units] ), dDate[Date] ) Units Sold YTD PRY = CALCULATE( [Units Sold YTD], SAMEPERIODLASTYEAR( dDate[Date] ) )
The subtle problem with year-to-date comparisons: if this year has data to March and last year has a full twelve months, comparing the two is meaningless. These measures pin the prior year to the same cut-off date.
Revenue MaxDate =
CALCULATE( [Revenue YTD], dDate[Date] <= MAX( fSales[Date] ) )
Revenue MaxDate PRY =
CALCULATE(
SUM( fSales[Revenue] ),
DATESYTD( SAMEPERIODLASTYEAR( dDate[Date] ) ),
dDate[Date] <= EDATE( MAX( fSales[Date] ), -12 )
)
Revenue PRY Full Year =
CALCULATE( SUM( fSales[Revenue] ), SAMEPERIODLASTYEAR( DATESYTD( dDate[Date] ) ) )
Revenue Diff ABS = [Revenue YTD] - [Revenue YTD PRY] Revenue Diff % = DIVIDE( [Revenue Diff ABS], [Revenue YTD PRY] ) Units Sold Diff ABS = [Units Sold YTD] - [Units Sold YTD PRY]
Raw ratings are noisy month to month, so the report smooths them before charting. The smoothed pair is what makes a genuine trend visible instead of sampling noise.
Score Average = AVERAGE( fSentiment[Score] )
Smoothed Score Average =
AVERAGEX(
DATESINPERIOD( dDate[Date], MAX( dDate[Date] ), -3, MONTH ),
[Score Average]
)
Smoothed Score Average Prev Month =
CALCULATE( [Smoothed Score Average], DATEADD( dDate[Date], -1, MONTH ) )
Score Diff %pt = [Smoothed Score Average] - [Smoothed Score Average Prev Month]
Note the distinction between Score Diff % and Score Diff %pt. A score moving from 60 to 63 is up 5% but up 3 percentage points, and quoting the wrong one overstates the change. Having both in the model, named clearly, is the sort of thing that stops a chart being misread.
MaxScore ALL = CALCULATE( MAX( fSentiment[Score] ), ALL( fSentiment ) ) MinScore ALL = CALCULATE( MIN( fSentiment[Score] ), ALL( fSentiment ) ) Last Refreshed = "Data refreshed: " & FORMAT( MAX( dDate[Date] ), "dd mmm yyyy" )
The fixed min and max are what let the gauge and the conditional colours keep a stable scale when the user filters, rather than rescaling to whatever is on screen and making everything look average.
Sales in USD as the headline, with top five products and a rated-products chart alongside. Deliberately sparse: the one number most people came for, plus enough context to decide which page to open next.
The commercial detail page and the busiest of the three, with twelve slicers covering product, category, manufacturer, segment and the full geographic hierarchy from country down to city. Year-to-date by category and by country sit next to the revenue difference measures, so absolute and relative performance are visible together. Explicit Close and Reset buttons are worth noting: with that many slicers, a reset control is not a nicety, it is what stops a user drawing conclusions from filters they forgot they applied.
The sentiment view. Year-to-date against prior year over time, ratings by category and region, top ten and bottom ten products, and a shape map for the geographic distribution of scores. The gauge uses the fixed min and max bounds so a filtered view still reads against the full scale.
The two halves sit side by side but never quite meet. The obvious next measure is a correlation view: score change plotted against revenue change per product, so the products where sentiment is moving ahead of sales, or behind it, separate out visually. That is the early warning a sales report cannot give on its own. I would also add a volume-weighted score, since a 4.8 average from twelve reviews and the same average from twelve hundred should not carry equal weight on a chart.