Relationships, Aggregation, Filter Paths, and Avoiding Double Counting

A correct relationship is not enough if aggregation is wrong

After grain and data quality are defined, we reach table relationships and how calculations respond to filtering. A common star-schema relationship connects one dimension row to many fact rows: one team has many requests, and one service type appears in many requests. This allows a filter such as “Team A” to identify the dimension row and reach related fact rows. But the existence of a relationship does not automatically tell us which aggregation to use or whether a field should be summed.

COUNT is not always entity count

The same table has three rows but two distinct requests. If the question is “How many part lines?”, row count = 3 is appropriate. If the question is “How many maintenance requests?”, distinct RequestID = 2. If the question is “Average part cost per request?”, calculate 100 ÷ 2 = 50, not 100 ÷ 3. The denominator must match the business entity named in the measure. This simple habit prevents many dashboard errors.

Aggregation is not limited to SUM and COUNT. Average, median, minimum, or maximum may be appropriate depending on the question and distribution. Mean handling time can be pulled by very long requests, while the median describes the middle value. No aggregation function is “best” without a question. Before choosing one, name the entity, grain, and unit, then test a small example that can be calculated by hand.

Filter context changes which rows enter a calculation

Filter context is the set of constraints that defines the data subset in which a measure is evaluated. If a card shows total part cost of 100 for all requests and the user selects team “North,” the calculation becomes restricted to request rows related to that team. The result might become 50 or another value depending on the data. That change is the meaning of interaction, not an error. The problem is when the user does not know a filter is active or when filtering reaches tables through an unintended relationship path.