Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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.
Solved! Go to 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
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
User | Count |
---|---|
128 | |
108 | |
99 | |
65 | |
62 |
User | Count |
---|---|
137 | |
115 | |
102 | |
71 | |
61 |