A per-region average changed after a row condition was added upstream; how does that differ from dropping whole regions with a group-level condition?

## Two places to narrow, two different things that move A grouped operation has an obvious seam: rows exist before the split and groups exist after it. A condition can sit on either side, and the choice is not cosmetic. - **Before the split, a condition evaluated once per row.** The rejected records never reach a group. Every group is formed from what survived, so every summary is computed over a smaller set. The *numbers* move. - **After the split, a condition evaluated once per group.** Every row reached its group and every summary was computed over all of them. The condition then tests those summaries and removes whole groups. The *membership of the result* moves, and the surviving numbers are unchanged. ## A worked case One region has eight orders: five large ones, and three at 20, 30 and 40. Its average order value over all eight is 220. 1. **Remove every order under 50, then group.** The three small orders never reach the region. The average is now computed over five rows and comes out higher — say 340. The region is still in the report, wearing a number that no longer describes what it did. 2. **Group, then drop regions with fewer than ten orders.** All eight orders counted toward the average, which stays at 220. The region has eight orders, so the condition drops it, and the report shows one region fewer with every remaining average untouched. Both runs excluded something small. They produce different reports, and only one of them changed any arithmetic. ## What each one can and cannot do | | Row condition before the split | Group-level condition after it | |---|---|---| | Changes a surviving group's summary | yes | no | | Removes a whole group | only by removing its last row | yes, by design | | Needs a summary to evaluate | no | yes | | Group sizes afterwards | smaller | unchanged | ## A row condition can also make a group disappear This is what makes the diagnosis ambiguous. If a row condition removes every row of a region, that region has nothing to summarise and simply is not in the result — unless the key column declares its allowed values and the tool emits the unobserved ones too, in which case it appears with an empty summary instead. Either way, a missing region is not proof that a group-level condition ran. The two absences mean different things: one is a region that failed a rule you wrote about regions, the other is a region eroded away by a rule about rows. ## Diagnosing a number that moved When a grouped figure changes and nobody touched the aggregation: 1. Compare **how many rows entered each group**, not just how many groups came out. A stable group list with moving numbers points upstream, at a row condition. 2. Compare the **group list**. A shorter list with identical surviving numbers points at a group-level condition. 3. If both moved, either two changes landed, or one row condition did both jobs: it shrank the groups it kept and emptied the ones it did not. ## The failure this causes in practice The characteristic bug is a report whose headline is a statement about groups and whose implementation is a condition about rows. 'Regions with meaningful volume averaged 340' is a sentence about regions; if the 340 came from dropping small orders, it is a sentence about orders wearing a region's name. Nothing in the output looks wrong, every number is internally consistent with every other, and the only way to catch it is to ask what entered each group. ## Choosing deliberately - Say which question the report answers: what our regions look like once small orders are excluded, or what our large regions look like. Those are different reports and deserve different labels. - Put the row condition where its effect on the summaries is intended, not where it was convenient to write. - A definition that is a statement about groups — at least ten orders, at least three months of history — belongs after the split, evaluated once per group, even when an equivalent-looking row condition is easier to express. - When both are needed, fix the order and keep it. Narrowing the rows first changes the summaries a later group-level condition tests, so the same two rules in the other order can accept a different set of groups.