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