Why is my dax measure still double counting work orders with the same serial number even though I tried to only include distinct workorder serialsials

Question

I want to count the quantity of parts used, but I have to first group the distinct product serial numbers and then group the distinct workorders and each group, sum up the column [actual qty], I see in my table that 2 rows with same workorder numbers are still being summed together

This 2 workorder each have the same product number, and each have an [actual qty] count of 2, (but i am unable to include it in this post because the table is too large), by right, I should get the sum to be =2, however, my code returns me an output of 4 in my table, which is double counting.

Here is my measure code

TotalQuantityPerProduct1 = 
SUMX (
    VALUES('PM Checklist vs Last SP Used'[Product]),  -- Get distinct product numbers
    CALCULATE (
        SUMX (
            VALUES('PM Checklist vs Last SP Used'[WO]),  -- Get distinct work orders
            CALCULATE (
                SUM('PM Checklist vs Last SP Used'[Actual Qty]-- Sum up actual quantity
            )
        )
    )
)

)

Answer 1

Ultimately, you have SUM(...), and even though it may be looking at just the one instance of WO (by value not row), it will SUM all the values that are of the same WO.

Options:

• Remove the Duplicate in PowerQuery
• Swap SUM with MIN or MAX - noting that if there are two of the same WO then it will pick the MIN/MAX value.

Your measure could look like:

TotalQuantityPerProduct2 = 
  SUMX(
    VALUES('PM Checklist vs Last SP Used'[Product]),
    SUMX (
      VALUES('PM Checklist vs Last SP Used'[WO]),
      CALCULATE (
        MAX ('PM Checklist vs Last SP Used'[Actual Qty])
      )
    )
  )