Count Qty of Parts in an assembly

[michaelmack]

Hello.

I have an invoiced quantity measure which counts sales of a part number.

eg.

A2412, A 2413, A2144.

I have also Kits that have different part numbers but are made up of the original part numbers.

eg.

K1 = 1x A2412, 4x A2413.

K2 = 2x A2412, 3x A2414.

Both kits and Single parts can be purchased.

When looking at total invoiced quantity the kits only show up as 1 part when it should actually show total qty of all parts used to make the kit.

I think I can attack this in several ways.

I could create a new table which includes the kit numbers and how many value it would represent or I could create a dax which explicitly states how many to substitute.

The list of kits is quite large though ~120 different kits, so maintinaing another table would be the preferred option i'm guessing.

Kit's can include up to around 30 parts.

Total Part numbers are in the thousands.

Wondering what your thoughts are on the best way to accurately count the total parts. What's the best approach?

Any example DAX to support pulling data from another table to manipulate only those part numbers that appear in the table otherwise default to the reported qty in the invoice quantity measure.

Do I need to put a 1 next to each part then tally the qty for the kits and reference that table somehow?

Thanks in advance for suggestions.

[johnt75 · Accepted solution]

I would create a Kits table which has 3 columns: Kit Part No, which is the part number for the kit as it appears in the invoice; Actual Part No, which is the base part number; Quantity. This table doesn't need to have a relationship to the invoice table.

You could then create a measure like

Invoiced quantity =
SUMX (
    Invoice,
    VAR CurrentPartNo = Invoice[Part no]
    VAR KitParts =
        CALCULATE (
            SUM ( Kits[Quantity] ),
            TREATAS ( { CurrentPartNo }, Kits[Kit Part no] )
        )
    RETURN
        COALESCE ( Invoice[Quantity] * KitParts, Invoice[Quantity] )
)

If the part number is a base part, rather than a kit, then the KitParts variable will return blank and so the COALESCE will return the invoice quantity.