Bill of Material - Multi level with Stock

Anything related to PowerPivot and DAX Formuale
Posts: 3
Joined: Wed Sep 06, 2017 11:46 pm

Bill of Material - Multi level with Stock

Postby iamnvt » Tue Dec 05, 2017 5:00 pm


I have read the article from Matt regarding M2M relationship, and apply to BOM calculation. This is very helpful indeed. ... ment-23150

I come up with a new problem when adding a stock table. Here is the attached file.!Aps8poidQa5zku85Cc_PuZ9nuAHeFw

To calculate the Material requirement, first, I will need to calculate the total of Sales of product, for example: Product A, total Sales = 15.
then need to deduct the available stock of A: 1 pcs --> net requirement of A is 14 pcs.
after multiply with BOM through sumx, I have the requirement of aa, bb, cc = 14
then deduct the stock of aa = 4 --> requirement aa = 10
then the requirement of child materials below a: e,f = 10, 20 accordingly.

What is the approach to do that in DAX? I am stuck at the step of deducting the stock. It doesn't give the correct result for the component requirement.


Posts: 6
Joined: Tue Jan 09, 2018 4:20 pm

Re: Bill of Material - Multi level with Stock

Postby P3Tom » Tue Jan 09, 2018 5:16 pm

I think your requirement is better resolved with a procedural language instead of DAX.

Return to “PowerPivot/DAX”

Who is online

Users browsing this forum: No registered users and 8 guests