ERP material usage formula

[Anonymous]

Hello Community - 

If you are familiar with ERP, and a BOM, you will understand what I am trying to do:    For each top level SKU, there is a certain amount of "material" required to make the SKU.    I am trying to calculate the material usage for any given item material, based on the order quantity of the top level SKU.  

Example:   In the screenshot below, there is an order for 12 of SKU ABC-1.    The corresponding material usage for a particular part of the BOM is 6.    So, the total material consumption = 72.   

Material Demand = [Sum of Material Qty Conv] * [Sum of QtyOrdered]    (these are both just SUMs of their respective columns).  

The Material Qty comes from Current Materials table.    The QtyOrdered comes from Sales Table.  

This looks fine if I cross filter or select an item from a slicer.   But when no slicers/filters are selected, the material demand gets wonky.    It is taking total material demand  * sum of quantity ordered which makes sense because that is what the measure says to do....but it's not what I need.  

I need the formula to step thru each particular top level SKU, and multiply it by the corresponding material usage - not aggregate it and then multiply.   Hopefully this makes sense.   

And here is how it looks if I click on a particular SKU from the Sales Table  (yes the sales table and the materials table are related).  In this example, we see that this particular SKU calls for 6 pcs of material 10526A.   And the correct material demand (4800) shows in the matrix table.   

[MFelix · Accepted solution]

Hi @Anonymous ,

In you description of the problem you actually refer the solution:

---

@Anonymous wrote:

I need the formula to step thru each particular top level SKU, and multiply it by the corresponding material usage - not aggregate it and then multiply.   Hopefully this makes sense.  

---

 In this cases you need to use the SUMX function, that will iterate over a table and make the expected values so in this case you would need to do something similar to:

USAGE = SUMX( Values(Bom[Sku]), [SUM of material qty conv]*[sum of qtyordered])

Be aware that this formula most probably will not work on your model, because I do not know how you have the setup of the BOM tables and the relationships between the other tables.

If you please can share a mockup data or sample of your PBIX file. You can use a onedrive, google drive, we transfer or similar link to upload your files.

If the information is sensitive please share it trough private message.

[Anonymous]

@MFelix     I had a chance to test this.  This was so simple and it worked.   Thanks a lot @MFelix  !

[MFelix]

Hi @Anonymous ,

Glad this worked, only question that could come up was about the setup of the BOM that because of parent/childf relation could be setup in a way that you would need to rephrase the syntax.

[Anonymous]

@MFelix    Hi Felix  -  Below is the model.   Not even 100% sure I have this setup correctly, but it does at least appear to be working somewhat.   

I have not tried your calculation yet, but I will in a few minutes.    Again, with nothing selected, I just get the aggregate * aggregate so it just shows me the sum of the total qty ordered * the material quantity.   But if I select on a given item, then the total shows correctly.