Power query formula

[Syndicate_Admin]

Hello All,

Happy new year 🙂

I would like to get your support to perform something that seems easy (and must be) but I can not figure it out (or better was able in a simple way which a huge work around manual calculation for each month which I have in my data set).

So the goal is to compute the cost of several products where we have the for each product a BOM - Bill of Material, the components costs could change over the months and make it more complex (if the cost of the components did not change over the months, and since the table of the components would be with unique values the connection of the tables make it kind of simple to handle).

So this is the goal, a Dax formula that compute the price per of the product over the component and take in consideration the months.

To make it easy attach we can find the example with the data set (summary excel file with the data loaded on the data model).

Hope this text/explanation was understandable, if not please let me know.

Thank you in advance

[Syndicate_Admin]

Hi Admin

Thank you for the input.

Generally speaking I understood the logic (merge tables). Obviously the scenario present returns a "limit" number of rows, on a real world scenarios we are handling with millions of rows. On this scenarios of millions of rows this solution present will keep to be the best "practice"?

I´m thinking as example if the data model will not be slow.

Thank you

[speedramps]

Please try this solution

Click here to download PBIX 

I have used Power Query to merge the various tables and create an exploaded BOM sales table.

The advanages of this solution is that all the heavy processing is done up front once, rather than each time you run a query.

It is also simply to understand and check, where the DAX for a BOM will be more complicated.

Hapy New Year !