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

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

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

      • Jdsarmientoc's avatar
        Jdsarmientoc
        Frequent Visitor
        6 years ago

        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!