Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Jdsarmientoc
Frequent Visitor

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 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:

 

KitItemQty per kit
1880661870971
1880661870961
1880661819049
1880671866491
1880671870961
1880671819049
2016811866491
2016811870961
2016811819048
1930881926321
1930881870962
19308818190418
2435802393831
2435801870961
2435801819049
2527322526941
2527321870963
25273218190427
2526932401461
2526931870962
25269318190418
​2541092535581
​2541091870961
​2541091819049
2481842401471
2481841819121
1880691866501
1880691819121
1880681870941
1880681819121
1880701786931
1880701786931
1880701786911
2626382614451
2626381819048

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.

2 ACCEPTED SOLUTIONS

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

View solution in original post

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

View solution in original post

11 REPLIES 11

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.