Converting data from Kits to components

[Jdsarmientoc]

Hi all!

Im currently working on a simple model to do the stock planning and production requirements for some items.

The thing is that some of those items are sold as Kits, therefore i would need to "explode" the sales and stock data to components.

One thing that is causing me some troubles is that the same item can be in multiple different kits:

Kit	Item	Qty per kit
188066	187097	1
188066	187096	1
188066	181904	9
188067	186649	1
188067	187096	1
188067	181904	9
201681	186649	1
201681	187096	1
201681	181904	8
193088	192632	1
193088	187096	2
193088	181904	18
243580	239383	1
243580	187096	1
243580	181904	9
252732	252694	1
252732	187096	3
252732	181904	27
252693	240146	1
252693	187096	2
252693	181904	18
​254109	253558	1
​254109	187096	1
​254109	181904	9
248184	240147	1
248184	181912	1
188069	186650	1
188069	181912	1
188068	187094	1
188068	181912	1
188070	178693	1
188070	178693	1
188070	178691	1
262638	261445	1
262638	181904	8

For example kit 188066 contains 1 187097, 1 187096 and 9 181904.

Keep in mind that components can also be sold individually.

Basically what i would like to do is remove the kits from the Stock and Sales tables and instead replace it with the amount of each component item.

Following example bellow if i sold 100 188066 i would like to remove that from the sales table and instead ADD 100 to 187097, 100 to 187096 and 900 to 181904 (same to stock table)

Thanks!

David.

[lc_finance · Accepted solution]

Hi David,

I modified a bit the solution to take into account that there is a single master table, and that kits can be identified based on the column Tipo.

You can download the updated solution from here.

And here is the new DAX formula:

Units sold = 

var currentProduct = [Product]

VAR productIsKit= LOOKUPVALUE('Masterdata'[Tipo],'Masterdata'[Product],currentProduct) = "Kit"

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

VAR productSalesViaKit = SUMX(
    'Masterdata',
    IF([Item]=currentProduct,
    [Qty per kit]*LOOKUPVALUE(Sales[Units sold],Sales[Item],[Product],0)
    )
)

RETURN IF(productIsKit, 0, productSales+productSalesViaKit)

It's very similar to before. The only main change is how to check whether the product is a kit. 

Now the variable productIsKit looks at the 'Tipo' column: if the Tipo column is equal to kit, then it considers the product to be a kit.

Hopefully, this matches all of your requirements

Let me know if anything is unclear

LC

[lc_finance]

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

[Jdsarmientoc]

@lc_finance Thanks a lot for your response!

Please excuse my delay in responding but im on a business trip with very limited time so havent been able to try your solution.

However i'd like to ask something.

On my data model the "Kit" SKU is on the same master file as the components (Basically there is a single master table ITEMS that contains both components and Kits, with all the attributes like description, weight, etc). One of this attributes indicates if the SKU is a kit (doing with a merge during the query between the master of items and the following kits table - red circle on the picture- looking for all ITEMS on the items table and matching with KITS on the kits table, then expanding TIPO). The query formulas are like this:

= Table.NestedJoin(#"Removed Duplicates", {"Item"}, #"Maestro Kits", {"Kit"}, "Maestro Kits", JoinKind.LeftOuter)

= Table.ExpandTableColumn(#"Merged Queries", "Maestro Kits", {"Tipo"}, {"Maestro Kits.Tipo"})

That way i have an attribute that indicates if the ITEM is a KIT or a component if this field is blank.

Finally on the table Pedidos (Sales) i add a calculated column to indicate if the sale is that item is a kit or not with the formula

Kit? = RELATED('Maestro items'[Maestro Kits.Tipo])

From what i can understand on your proposed solution there should be a master table for components and a master table for Kits, or is this not the case?

Rest assured that i will try your solution asap and get back to you!

Thanks in advance,

David.

[lc_finance · Accepted solution]

Hi David,

I modified a bit the solution to take into account that there is a single master table, and that kits can be identified based on the column Tipo.

You can download the updated solution from here.

And here is the new DAX formula:

Units sold = 

var currentProduct = [Product]

VAR productIsKit= LOOKUPVALUE('Masterdata'[Tipo],'Masterdata'[Product],currentProduct) = "Kit"

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

VAR productSalesViaKit = SUMX(
    'Masterdata',
    IF([Item]=currentProduct,
    [Qty per kit]*LOOKUPVALUE(Sales[Units sold],Sales[Item],[Product],0)
    )
)

RETURN IF(productIsKit, 0, productSales+productSalesViaKit)

It's very similar to before. The only main change is how to check whether the product is a kit. 

Now the variable productIsKit looks at the 'Tipo' column: if the Tipo column is equal to kit, then it considers the product to be a kit.

Hopefully, this matches all of your requirements

Let me know if anything is unclear

LC

[Jdsarmientoc]

Dear @lc_finance ,

Thanks a lot for your continuous support and input. 

Im very new to DAX (for example first time i see those variables) so i dont know what im doing wrong when i try to emulate your solution in my model but i cant make it work.

If you are willing to i have opted to share you my model so you can help me. You can download here .

Again the final result i need is to show those sales month by month but only by non-kit items (kit items already "exploded" into their component parts and added to the component sale). This is to plan a forecast.

Hope you can help me.

Best regards!

David.

[lc_finance]

Hi David,

I should have some time tomorrow to look at it and then get back to you.

And no worries, I will make sure we find a solution to your formula issues.

Regards

LC

[lc_finance · Accepted solution]

Hi David,

I updated your file to include the calculation.

You can download it from here.

I hope that this is what you are looking for. If you need more help, do not hesitate to ask.

LC

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

[Jdsarmientoc]

Dear @lc_finance 

Im exploring your solution and comparinb vs the result i get when doing the consolidation of kits into items manually but just noted something:

Take for example item 178690. This item is NOT a kit nor a component of a kit. This is a regular item that is sold individually therefore this should have the same amount on the original sales table and the calculated one. However im noting that this changes.

This is using the original Pedidos table:

Using Pedidos by item calculated table

And this is using the Pedidos by item calculated table:

Using original Pedidos table

As you can see some months are the same as it should but some months they are different.

Finally the last thing i would like to ask your help with is with the fields Inv (stock) and BO (backorder). I would like to also "explode" them by component item, just like the sales. This one is not needed month by month, just the total of current Inv (stock) and total current BO (backorder) per item. Since this is similar to what you did with Pedido (sales) i will try to emulate it but would love your help in case i cant.

Best regards!

[lc_finance]

Hi @Jdsarmientoc ,

I apologize for the time it took getting back to you, I just got married this week.

Anyway, you can download my updated solution from here.

The units sold formula needed an update. Here is the new version of the formula:

Units sold = 

var currentItem = [Item]
var currentDate = [Fecha]

VAR itemIsKit= LOOKUPVALUE('Maestro items'[Maestro Kits.Tipo],'Maestro items'[Item],currentItem) = "Kit"

VAR itemDirectSales = CALCULATE(SUM('Pedidos'[Pedido]),'Pedidos'[Item]=currentItem,'Pedidos'[Fecha]=currentDate)

VAR itemSalesViaKit = SUMX(
    'Maestro Kits',
    IF([Item]=currentItem,
    var currentKit = [Kit]
    RETURN [Cantidad x kit]*CALCULATE(SUM('Pedidos'[Pedido]),ALL(),'Pedidos'[Item]=currentKit,'Pedidos'[Fecha]=currentDate)
    )
)


RETURN IF(itemIsKit, 0, itemDirectSales+itemSalesViaKit)

I tried it on the example you provided and it works well now.

Is this what you are looking for?

For the explosion of stock and back-order, yes you can try to emulate it and get back to me if any issues.

Regards,

LC

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

[Jdsarmientoc]

Hello @lc_finance 

First of all contratulations on getting married! Wish you the best.

Your solution works great now and i also wanted to share an alternate very noob-friendly solution that i came up with using not DAX but the query editor instead.

I created a new table merging Pedidos and Maestro Kits and expanding Item and Qty per kit. The query editor code looks like this:

let
Source = Table.NestedJoin(Pedidos, {"Item"}, #"Maestro Kits", {"Kit"}, "Maestro Kits", JoinKind.LeftOuter),
#"Expanded Maestro Kits" = Table.ExpandTableColumn(Source, "Maestro Kits", {"Item", "Cantidad x kit"}, {"Maestro Kits.Item", "Maestro Kits.Cantidad x kit"})
in
#"Expanded Maestro Kits"

That way i get something like this:

In red for example is what  used to be a single line for item 188066 of 4 units.

Then in the model i add 2 columns for this table, one for Qty and one for Item component, as follows:

Qty = if([Maestro Kits.Cantidad x kit]=BLANK();[Pedido];[Pedido]*[Maestro Kits.Cantidad x kit])

Item comp = if([Maestro Kits.Cantidad x kit]=BLANK();[Item];[Maestro Kits.Item])

That way if the expanded field on the merge is blank it means it is an item that is NOT part of a kit so it uses de original SKU and Qty, but if it is not blank that means it is part of a kit and calculates the QTY and replaces the KIT SKU with the component SKU.

Best regards and thanks a lot for bearing with me!

[lc_finance]

Nice work for the solution with the Query Editor!

I like the way you approached it with Query Editor, and it might also be a performing way for the conversion of kits to components.

Let me know if you need anything else,

LC

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

[lc_finance]

Hi @Jdsarmientoc ,

I will have some time tomorrow, I'll look into this and get back to you

LC