Hierarchal Relationships

[PCI_Powerbi]

Hello, 

Helping a colluege on an issue that I believe deals with hierarchal relationships between tables? I've spent some time trying to solve this but have been stumped so far. Any and all input is welcome.

The problem resolves around determining an investors total position based on their various investments. The trick lies in where an investor is invested in a certain entity which in turn is invested in another entity.  See sample data below.  While currently shown in 3 individual tables ideally the data would live in one table and you could extract a total investment for each individual investor.

Let me know what you think!

[OwenAuger · Accepted solution]

Hi @PCI_Powerbi 

PBIX with some suggestions attached.

1. I would suggest you structure your source data as a table containing all direct investment Ownership values by Investor/Investee:

2. Then in Power Query self-join this table iteratively to produce all investment paths, multiplying the Ownership value at each step. There are some additional classification columns added (see the M code in PBIX).

The result is a table containing all investment "paths" where Ownership is the product of the direct Ownership values along that path.

3. To produce your sample output, you would filter on Investor Type = "Top Level" and Investee = "Company A"

There are likely some tweaks needed to support your reporting needs but hopefully this is a start.

(apologies @amitchandak , I saw you also replied just before I did)

Regards

[PCI_Powerbi]

Thank you very much! This looks like it will get me on the right track.

[amitchandak]

@PCI_Powerbi , You should add company column in each table and append them togther in Power Query

Or add company column and Join with Dimension, COmpany and Inversor and then analyze them together

[PCI_Powerbi]

Thank you for the info!