Converting data from Kits to components

Hi Jdsarmientoc ,

Thank you for the interesting question!

You can download my proposed solution from here.

Here is how I would approach it:

1) Create a new calculated table ' sales by component' which includes components that are sold individually (from the Sales table) and components sold as part of kits (from the Kits table). Here is the formula for the calculated table:

Sales by component item = DISTINCT(
  UNION(
    VALUES(Sales[Item])  
  , VALUES(Kits[Item])
  )
)

2) Create a calculated column in this table to estimate the units sold by component. Units sold by component can come either from:

- the individual sale of the component itself

- the sale of the component as part of the kit

here is the formula for the calculated column:

Units sold = 

var currentItem = [Item]

VAR itemIsKit= 
    NOT COUNTX(
        FILTER('Kits',
        [Kit]=EARLIER('Sales by component item'[Item])
    ),[Kit]) 
     = BLANK()

VAR itemSales = LOOKUPVALUE(Sales[Units sold],Sales[Item],[Item],0)

VAR itemSalesViaKit = SUMX(
    'Kits',
    IF([Item]=currentItem,
    [Qty per kit]*LOOKUPVALUE(Sales[Units sold],Sales[Item],[Kit],0)
    )
)

RETURN IF(itemIsKit, 0, itemSales+itemSalesViaKit)

Here is what I have in the sales table (188066 is a kit while 190000 is an individual component):

And here is what I have in the ' sales by component' calculated table.

The product 190000 is not a kit, so sales are kept as it. 

The product 188066 is a kit, so its sales are split by component: 100 to 187097, 100 to 187096 and 900 to 181904

Hope this helps you! Do not hesitate if you have further questions,

LC

Interested in Power BI and DAX website? Check out my blog at www.finance-bi.com