Calculating with values linked throughout a child-parent hierarchy

[roelandgeorge]

The bill of material for each end product consists of subproducts that are present in a certain percentage within the end product. These subproducts, in turn, may also consist of a certain percentage of other subproducts or raw materials, up to 7 levels deep. The desired outcome is to determine the percentage of raw materials comprising each end product, bypassing all the unnecessary links of subproducts. Essentially, this involves multiplying percentages.

I begin with a table of Bill Of Material (BOM) where the first column represents the end product (the parent), the second column represents the component (subproduct or raw material), and the third column represents the percentage. This table contains 90,000 rows and takes 2 minutes to load.

My question is: How can I perform this multiplication?

Using DAX, a simple path does not work immediately because the components are used in different end products so multiple parents.

One approach I found is to first flatten the data using the "dynamic flattening parent-child hierarchies" function found here: https://www.thebiccountant.com/2017/02/14/dynamically-flatten-parent-child-hierarchies-in-dax-and-po.... This would allow me to see all values side by side, but with 90,000 rows, it cannot handle this.

If they were arranged side by side though, I could then add a calculated column that multiplies each value from columns 1 to 7 together.

This method DOES work with a small number of rows, as demonstrated in the example just to let you understand which calculation I need.

There must be another way without having to list out every linked item for each article (using relationships in Power BI, DAX formulas, M formulas in Power Query, etc.).

Pictures and PBIX-file with only one end product as an example:

For the end product 4000000, we aim to determine the proportion of the four raw materials. For example the percentage of 3000002 in 4000000 is 45%.

Below the slow way to do it:

Link to the pbix file:

BomFlatteningExample.pbix

[v-jianpeng-msft]

Hi, @roelandgeorge 

Using flattening in Power Query is indeed one way to handle parenting. In addition, you can also use the DAX function PATH with the Lookupvalue function. You can refer to the following articles to deal with your parent-child calculations:

Parsing Organizational Hierarchy or Chart of Accounts in Power BI with Parent-child Functions in DAX...

In this article, we use the two functions mentioned earlier. DAX does not directly support parent-child hierarchies. To get a browsable hierarchy in the data model, you must naturalize the parent-child hierarchy. The following article is another way to deal with parent-child computations:

How to Get Your Question Answered Quickly

If it does not help, please provide more details with your desired output and pbix file without privacy information (or some sample data) .

Best Regards

Jianpeng Li

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[roelandgeorge]

Hi Jianpeng, thanks for the response, however using DAX, a simple path does not work immediately because the components are used in different end products so multiple parents. I'm wondering if I can use other formulas.

[v-jianpeng-msft]

Hi, @roelandgeorge 

Thank you very much for your reply. There are currently a limited number of methods and functions for dealing with parenting, and it is recommended that you read the following methods for dealing with the problem and somehow apply them to your data.

Understanding functions for parent-child hierarchies in DAX - DAX | Microsoft Learn
Clever Hierarchy Handling in DAX - SQLBI

powerquery - Dynamically flatten ParentChild Hierarchy using Power Query in PowerBI - Stack Overflow

How to Get Your Question Answered Quickly

If it does not help, please provide more details with your desired output and pbix file without privacy information (or some sample data) .

Best Regards

Jianpeng Li

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.