Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
Syndicate_Admin
Administrator
Administrator

Power query formula

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

 

2 REPLIES 2
Syndicate_Admin
Administrator
Administrator

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
Super User
Super User

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 !

 

 

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

Find out what's new and trending in the Fabric community.

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.