Why product detail changes your sales dashboard total

Ask what one row means

If the branch and period stay fixed, simply displaying product detail should not change an additive sales total. A change is a reason to inspect the underlying rows, joins and calculation. It is not yet proof of a particular fault: a product filter, missing product mapping or different date basis can also change which transactions qualify. Save the filters and refresh time before comparing views.

The grain is the business event represented by one row: an order, an order line, or a branch's monthly target. Microsoft's star-schema guidance says fact tables should have consistent grain; descriptive tables support filtering and grouping. Apply that principle by writing the row definition beside every monetary field. A header amount repeated after a join remains an order amount, even though the screen now displays product lines.

Trace a fictional sale through the report

Consider fictional order MK-104 at a Mong Kok branch, posted on 12 October 2026. It has three lines and a header total of HK$600. All amounts below are invented teaching values in HKD, after line discounts, with no delivery charges, returns or other adjustments. Assume one unit per line and no order-level discount. Joining the header to its lines repeats HK$600 three times; summing that repeated column produces HK$1,800, although the sale is still HK$600.

Arithmetic demonstration only: 300 + 200 + 100 = 600; 600 × 3 = 1,800. No observed business results are represented.

Do not repair this by taking the maximum amount across the whole report or removing duplicate monetary values. Two different orders can legitimately have the same total. Keep order amounts at a unique order key when reporting orders, and use line amounts for product sales. If the source only provides an order total, product revenue needs an agreed allocation rule; until then, show it as unavailable. A product-level order count may overlap across products, so its rows need not add up to the distinct order count.

Keep the monthly target at its own level

Now give that branch an invented October target of HK$60,000, with MK-104 as its only posted sale so far in this example. The full-month target remains HK$60,000 when the three product rows appear. Copying it onto those lines and summing would produce a false HK$180,000 denominator. Correct month-to-date attainment against the full-month target is 600 ÷ 60,000 = 1%. This is not a daily pace forecast. Without an approved product allocation, product-level target and attainment should be blank or explicitly unavailable.

Proposed definitions, not universal accounting rules. Record the source fields, currency, exclusions, target version, owner and approval date alongside this sheet.

Have the accounts owner settle the commercial meaning before the report developer changes formulas. Decide whether returns reduce the month of the credit posting or restate the original sale month, and whether delivery income belongs in sales. Preserve the approved target version. For a daily or product drill-down, either withhold unsupported targets or document a deliberate allocation whose parts reconcile to the original target. More detailed sales data does not create more detailed planning data.

Reconcile a small sample before accepting the fix

Use a frozen source export and report snapshot with the same posting cutoff, permissions and branch filters. Start with the fictional case as an arithmetic test, then select actual authorised records: a multi-line sale, a discount, a credit, a month-end posting and another branch. Include two orders with equal totals and any unmapped product. Small samples diagnose errors; they do not prove the whole month is correct. Keep customer names out of the working comparison where document keys suffice.

1. Match the source records

   List document and line keys, branch, product, posting date and signed net amount. Check for duplicate keys and record every unmatched line.

2. Compare each processing stage

   Compare row count, unique line count and amount before and after each join. Trace any extra or missing line back to its document.

3. Repeat the drill-down

   With identical filters, compare the sales card, branch subtotals and product rows including unknown products. Reconcile the monthly target separately; test that the two equal-value orders both remain counted.

4. Extend to the full period

   Reconcile all included documents and branch totals to the frozen export. Log expected value, actual value, difference and explanation; rerun after refresh before the owner signs off.

Choose a focused report repair when source amounts and identifiers are sound and the defect is confined to joins, relationships or measures. A temporary branch-level report is a reasonable alternative while product allocation is unresolved. If source documents lack reliable line identifiers or disagree internally, investigate that upstream issue first. Do not approve a platform replacement solely because one dashboard total changes. For identical inputs, require zero unexplained monetary difference; any rounding tolerance must state its precision and calculation basis.

Give the report owner a concrete next action

At the next reporting meeting, ask the accounts owner and report maintainer to complete the definition sheet for sales, target and attainment, then bring one failing document through the reconciliation sequence. The repair brief should name the affected views, approved definitions, test records, expected totals, refresh test and handover owner. Astera's partner-selection guide can help make acceptance and support explicit when commissioning this work; the custom software service page provides context if a scoped reporting change needs implementation support.