Rollups That Disagree With the Source
A Sales table has 1,200 records. Each sale links to one Rep and one Region. A Reps table uses a rollup to sum sale amounts per rep, and a Regions table uses a rollup to sum sale amounts per region. Finance reports that the sum of all rep rollups equals $4.2M but the sum of all region rollups equals $3.9M. Every sale has a rep; about 60 sales have a blank Region. Additionally, 15 sales link to two reps because a shared-deal field was misused, and those sales' amounts are counted in both reps' totals. Finance wants a single authoritative revenue number.
1. Explain each discrepancy source and which rollup is 'more correct' for total revenue.
Read the worked answer
Two independent causes:
- Blank Region (60 sales): a rollup only counts linked records, so sales with no Region are excluded from region totals. The $300K gap is consistent with those 60 sales being unassigned. Region rollups undercount by construction.
- Double-linked reps (15 sales): a sale linked to two reps is counted in both rep rollups, so rep totals overcount by the duplicated amounts.
Neither rollup is authoritative for total revenue: rep totals overcount (double links) and region totals undercount (blanks). The authoritative figure must come from the Sales table itself (a count/sum over all 1,200 sales), not from summing rollups. Rollups are per-parent aggregates and are not designed to reconcile to a grand total when links are many-to-many or missing.
2. How would you produce one trustworthy revenue total and reconcile it to both rollups?
Read the worked answer
- Source of truth: sum the Sale Amount field directly on the Sales table (a view or a summary bar), giving the true total.
- Reconcile reps: true total + (sum of the 15 double-counted amounts) should equal the sum of rep rollups; if not, there is another error.
- Reconcile regions: true total − (sum of the 60 blank-region amounts) should equal the sum of region rollups.
- Fix the cause: enforce one rep per sale (or model shared deals with a separate split field) and require a Region on every sale, or add an 'Unassigned' region so blanks are visible rather than invisible.
- Document that rollups are aggregates per parent and must be reconciled, not summed, when links are not strictly one-to-many.
3. What structural change prevents the shared-deal double count without losing the shared-deal information?
Read the worked answer
The double count comes from using a many-to-many link where the rollup assumes one-to-many. Options:
- Split the amount: keep the many-to-many link but add a 'Rep share' field per link (via a junction table of Sale-Rep with a share percentage), and roll up
Amount × Shareso shares sum to 100%. - Primary rep + collaborators: keep one authoritative Rep link for revenue attribution and a separate multi-link 'Collaborators' field that does not feed the revenue rollup.
- Junction table is the cleanest if deals are genuinely shared: Sale → Deal-Rep records → Rep, with the rollup summing shares. This preserves shared-deal visibility while making the revenue rollup sum correctly.