The source records
Imagine a source table with one row for Order A and an order value of $120. A second table records its two shipments, Shipment 1 and Shipment 2. Both shipment rows carry the same order identifier so they can be related to the order.
The order table has an order grain. The shipment table has a shipment grain. Their values can be individually correct. The relationship is one order to several shipments, which is important before writing the query.
These are made-up records used to explain the calculation. They are not a client dataset or a claim about an existing reporting system.
One order contributes its value once.
What the joined rows contain
A join on the order identifier creates a row for Order A with Shipment 1 and another row for Order A with Shipment 2. The order amount is present on both rows: $120 followed by $120.
A sum over that repeated order amount returns $240. The query has not changed the source value. It has changed the set of rows over which the value is aggregated. The joined table now describes an order-shipment relationship rather than one row per order.
Adding a filter that happens to select one shipment can make the total appear correct for this example. It also changes which shipment information the report includes. That is a different decision from calculating accepted order value.
Choose the measure before repairing the query
If the question is accepted order value, calculate that amount from a relation that contributes each included order once. Shipment information may still be useful for filtering, but the filter should not cause a repeated order contribution.
If the question is the value delivered in each shipment, the model needs an agreed shipment-level value. That could come from source shipment lines or a defined allocation using quantities and prices. It cannot be inferred simply by dividing $120 by the number of shipment rows.
The two shipments might contain different items or quantities. Allocating $60 to each would introduce a new commercial assumption. A total that adds back to $120 does not establish that each shipment has the correct amount.
Why DISTINCT needs care
Removing duplicates can be appropriate when the relation contains duplicate records under the intended key. Applying DISTINCT to the amount itself answers a different question.
Suppose a second, separate order also has a value of $120. A distinct set of amounts contains one $120 value even though the two orders contribute $240 together. The record's identity, rather than the coincidence of equal values, determines whether it should contribute once.
That is why a repair should name the intended row grain and key. A small set containing a multi-shipment order and two separate orders with the same amount tests more than the first example alone.
Checks for the model
Inspect row counts at the expected key before and after the join. Test an order without a shipment, an order with one shipment and an order with several. Establish which of those should appear in the chosen measure.
Then check record-level contributions as well as the aggregate. A missing order and a duplicated order can offset each other in a total. A report can also return the right overall value while assigning it to the wrong product or period.
Keep the intended relationship explicit in the model. Retain the example as a test so a later join change is checked against the same order and shipment records.