Forum Discussion

Jdsarmientoc's avatar
Jdsarmientoc
Frequent Visitor
6 years ago
Solved

Converting data from Kits to components

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...
Need Help
  • lc_finance's avatar
    lc_finance
    6 years ago

    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's avatar
    lc_finance
    6 years ago

    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

lc_finance's avatar
lc_finance
Solution Sage
6 years ago

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's avatar
Jdsarmientoc
Frequent Visitor
6 years ago

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's avatar
    lc_finance
    Solution Sage
    6 years ago

    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