Blended Quotas Inflate Revenue by 12%

Mechanism

Blending does not join row-by-row. Tableau queries the primary, queries the secondary, and aggregates the secondary up to the level of the link dimensions present in the view, then stitches the results. Two consequences follow:

1. Quota repetition. The view contains Order ID. Quota lives at Rep ID. Tableau therefore returns quota at the finest link level it can satisfy, so each rep's $ quota is attached to every order-line group that rep owns. SUM(Quota) becomes quota × number of order groups, which is why $38M renders as $41M. The size of the gap scales with view granularity, not with the data - a tell-tale sign.
2. Fan-out from duplicate link keys. If the Excel file contains more than one row per Rep ID (a mid-year quota revision, a blank trailing row, an alternate-rep row), the blend produces multiple secondary rows for one primary row, and primary measures get counted more than once. That is what inflates SUM(Line Amount).

Confirmation before changing anything

• COUNTD([Rep ID]) and COUNT([Rep ID]) on the Excel source. Any difference is the duplicate set.
• Build SUM(Line Amount) by Rep ID from the Orders extract alone, then the same from the blended sheet, and diff the two lists. The inflation should concentrate in the handful of reps with duplicate quota rows - if it is spread evenly, the cause is granularity, not duplication.
• Remove Order ID from the view and drop to Rep ID only. If quota snaps back to a sane per-rep number, granularity is confirmed as the trigger.
• Check for a NULL or blank Rep ID in the Excel file; unlinked rows are silently dropped from the blend and quietly shrink the quota base.

What breaks now

A ratio is only meaningful if numerator and denominator are aggregated at the same grain. In the current blend:

• SUM([Line Amount]) / SUM([Quota]) at Order ID grain has a quota denominator inflated by repetition, so attainment collapses toward zero as the view gets finer. Users will read this as "we are at 5% of plan" and lose trust in the workbook.
• AVG([Line Amount]) / AVG([Quota]) is worse - it averages a line-item amount against a rep-level number and is not a ratio of totals at all.
• Any attempt to "fix" it by adding MIN([Quota]) or a FIXED LOD on top of the blend papers over duplicates rather than removing them, and silently hides the next data quality problem.

Recommended design

Move the aggregation down into the data layer, not the view:

1. Deduplicate the quota file to exactly one row per Rep ID (decide the business rule for revisions - latest effective date wins - and encode it).
2. Either join Orders → deduplicated quota as a many-to-one physical join, or (cleaner) pre-aggregate Orders to rep-day or rep-month in its own source and join that to quota, so the join key is unique on both sides.
3. With a unique one-to-many-at-most join, attainment becomes SUM([Line Amount]) / SUM([Quota]) - but it is only correct at Rep ID or coarser. At region level, SUM(Quota) is correct only because each rep appears once; verify with a SUM([Quota]) region total against the file's $38M.
4. Sanity-check the denominator independently: put SUM(Quota) on a plain text table by region, no other fields, and reconcile to source. If that number ever moves when you add a dimension, the model is still wrong.

Guardrail: publish a rep-level attainment view as the certified source and let dashboards aggregate up from it, rather than letting each author rebuild the ratio.

Why

Filters are scoped to a data source. A quick filter on [Region] from the primary source restricts the primary query only. The secondary query is narrowed only by the link dimensions that actually appear in the view - and Region is not a field in the quota file at all, so there is nothing to pass down. Tableau cannot push a filter onto a column the secondary does not have.

Options

1. Add Region to the secondary. Bring a rep→region mapping into the quota source so Region exists on both sides, then place Region in the view. The blend aggregates at Region and the totals line up. Cheapest fix, but it hard-codes the region definition in two places - they will drift.
2. Filter at the view level. Put Region on the Filters shelf with "All Using This Data Source" plus an explicit second filter on the secondary. Works, but two filters to maintain and users will break it.
3. Replace the blend with a join on a deduplicated rep dimension that carries Region, Quota and Hire Date. One filter, one region definition, correct totals at every level. This is the right answer if the workbook is going to be maintained; it costs a rebuild and a refresh-schedule change.
4. Row-level security as a separate concern. If the region filter exists to restrict what a manager can see, it belongs in a data source filter or the entitlements table - not in a quick filter that any user can clear.

Recommendation: option 3 for correctness, option 1 only as a stopgap with a note that region is defined twice and must be reconciled on refresh.