Blog · Board and management reporting
A retail dashboard should explain sales and gross profit by store and category. Follow a worked example that reconciles returns, costs, and stock movements before interpreting the change.
A retail dashboard in Power BI should explain net sales, gross profit, and stock by store and category, with totals that reconcile to the exports. Sales growth alone is incomplete: refunds, merchandise cost, and the availability of the items customers want can change the decision.
Microsoft's retail analysis example compares sales, units, gross margin, and performance across stores and districts. The example below focuses on checking those business measures before presenting them. Covirage is not affiliated with Microsoft.
Use the sales export for gross merchandise sales, refunds and allowances, and net units sold. Add merchandise cost for those same sales, plus inventory movement and closing balances for the same locations. Keep sales tax outside merchandise revenue in this example, and use USD throughout.
Define the period once. Here, a fictional Ohio home-goods retailer reviews a completed week across Columbus and Dayton, grouping merchandise into essentials and seasonal items. The prior comparison week includes the same stores and category definitions. All amounts below are illustrative company records, with no missing locations or unallocated costs.
The IRS guide to figuring gross profit deducts returns and allowances from gross receipts and then deducts cost of goods sold. That sequence is also a useful check for the management view: refunds change net sales, while the cost column must reflect the company's matching cost entries.
Net sales = Gross sales − Returns and allowances
Gross profit = Net sales − Cost of goods sold
Gross margin = Gross profit / Net sales
Keep gross margin separate from operating profit. Store payroll, rent, and other expenses outside the reported merchandise cost are not deducted in this example. The gross profit margin guide covers the broader definition; here the task is explaining the store-and-category movement.
The current-week table contains the financial inputs and their calculated results. Dollar amounts are exact. Margins are rounded to one decimal, but total calculations use the dollar amounts rather than rounded percentages.
| Store and category | Gross sales (USD) | Returns (USD) | Net sales (USD) | COGS (USD) | Gross profit (USD) | Margin |
|---|---|---|---|---|---|---|
| Columbus: essentials | 12,000 | 400 | 11,600 | 7,000 | 4,600 | 39.7% |
| Columbus: seasonal | 10,000 | 1,000 | 9,000 | 5,400 | 3,600 | 40.0% |
| Dayton: essentials | 10,000 | 500 | 9,500 | 6,000 | 3,500 | 36.8% |
| Dayton: seasonal | 8,000 | 100 | 7,900 | 4,300 | 3,600 | 45.6% |
| Total | 40,000 | 2,000 | 38,000 | 22,700 | 15,300 | 40.3% |
For Columbus seasonal, net sales are 10,000 − 1,000 = 9,000. Gross profit is 9,000 − 5,400 = 3,600, and margin is 3,600 / 9,000 = 40.0%.
The company check is 40,000 − 2,000 = 38,000 net sales, followed by 38,000 − 22,700 = 15,300 gross profit. Company margin is 15,300 / 38,000 = 40.3%. Reconcile the first result to the sales report and the second to the management profit statement using the same cost policy.
Roll the same rows up in both directions:
| View | Net sales (USD) | Gross profit (USD) | Gross margin |
|---|---|---|---|
| Columbus | 20,600 | 8,200 | 39.8% |
| Dayton | 17,400 | 7,100 | 40.8% |
| Essentials | 21,100 | 8,100 | 38.4% |
| Seasonal | 16,900 | 7,200 | 42.6% |
Store net sales sum to 38,000, as do category net sales. Both gross-profit views sum to 15,300. Do not add the store rows to the category rows; they are two views of the same transactions.
The prior-week inputs are below. This gives the comparison an auditable base rather than a percentage with no supporting dollars.
| Store and category | Prior net sales (USD) | Prior COGS (USD) | Prior gross profit (USD) |
|---|---|---|---|
| Columbus: essentials | 11,000 | 6,600 | 4,400 |
| Columbus: seasonal | 10,000 | 6,000 | 4,000 |
| Dayton: essentials | 9,000 | 5,400 | 3,600 |
| Dayton: seasonal | 7,000 | 4,200 | 2,800 |
| Total | 37,000 | 22,200 | 14,800 |
Net sales increased by 38,000 − 37,000 = $1,000, or 1,000 / 37,000 = 2.7%. Gross profit increased by 15,300 − 14,800 = $500, or 500 / 14,800 = 3.4%. Margin moved from 14,800 / 37,000 = 40.0% to 40.3%, an increase of 0.3 percentage points after rounding.
The store manager can see the gains and losses directly. Dayton seasonal gross profit rose from $2,800 to $3,600, while Columbus seasonal fell from $4,000 to $3,600. Investigate the category rows before describing the whole company movement as stronger demand or better pricing; the summary does not isolate those causes.
These unit records use the same current week. Net unit sales already include saleable returns, receipts include all additions, and there are no transfers, shrink adjustments, or other movements in this simplified example.
| Store and category | Opening units | Received units | Net units sold | Closing units | Closing cover (weeks) |
|---|---|---|---|---|---|
| Columbus: essentials | 500 | 40 | 420 | 120 | 0.29 |
| Columbus: seasonal | 500 | 40 | 280 | 260 | 0.93 |
| Dayton: essentials | 450 | 50 | 350 | 150 | 0.43 |
| Dayton: seasonal | 430 | 40 | 250 | 220 | 0.88 |
| Total | 1,880 | 170 | 1,300 | 750 | 0.58 |
The stock identity is opening + received − sold = closing. For the company, 1,880 + 170 − 1,300 = 750 units. Closing cover is closing units divided by this week's net unit sales, so Columbus essentials has 120 / 420 = 0.29 weeks, rounded to two decimals.
Use that as a recent sales-rate indicator. It does not show when stockouts occurred or predict next week's demand. A category can have stock in total while a popular item is unavailable, so the merchandiser should inspect item-level exceptions before moving inventory.
Suppose Columbus seasonal returns increase from $1,000 to $2,000 because of an additional customer allowance. This is a refund without a physical return or cost reversal; COGS and units stay fixed. Only the returns input changes.
| Measure | Original | Revised |
|---|---|---|
| Columbus seasonal returns (USD) | 1,000 | 2,000 |
| Columbus seasonal net sales (USD) | 9,000 | 8,000 |
| Columbus seasonal gross profit (USD) | 3,600 | 2,600 |
| Columbus seasonal margin | 40.0% | 32.5% |
| Company net sales (USD) | 38,000 | 37,000 |
| Company gross profit (USD) | 15,300 | 14,300 |
| Company margin | 40.3% | 38.6% |
The revised category margin is (10,000 − 2,000 − 5,400) / (10,000 − 2,000) = 32.5%. Company margin becomes 14,300 / 37,000 = 38.6%. The dashboard must show the refund change rather than presenting the lower margin as a merchandise-cost increase.
For page structure, use Power BI dashboard examples; the executive dashboard examples cover the leadership summary. The Power BI alternatives guide discusses the broader reporting approach when your immediate question starts in exports.
Bring your store-and-category sales export, matching costs, and stock movements, or a Google Sheet with those fields. Covirage maps the columns; its tools compute and check the sales, margin, and inventory figures, and the external AI model explains changes with every figure traced to its rows. Create a free Covirage account, which takes about a minute, and bring a sample of your own data.
See revenue driver analysis for the supporting business context.
Include returns, cost of goods sold, gross profit, gross margin, and an inventory measure tied to the same stores and categories. Sales growth can coexist with falling profit or shortages, so the review should connect demand with its financial and operational consequences.
Calculate company gross margin from total gross profit divided by total net sales. A simple average of store percentages gives a small location the same influence as a large one. A net-sales-weighted average is equivalent when calculated without premature rounding.
It can provide a clearly labeled snapshot, but it is sensitive to promotions, closures, and seasonal demand. Divide closing units by that week's unit sales only when the period and unit definitions match, and avoid presenting the result as a demand forecast.
Show them as a separate deduction from gross sales, with a consistent posting policy. Determine whether a return restores saleable stock and reverses cost of goods sold. Refund value, inventory movement, and cost reversal are related records that may differ.