Forum Discussion
ERP material usage formula
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.
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.
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.
4 Replies
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.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.
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.