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 ,

 

 

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

    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'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.