How to embed the financial data based on ownership % of child company to its parents

[minhquankhong]

Hi All,

I need help to include the child company's financial data (12CDE0001) based on its parent ownership % to its parents' financial data (11ABC0001 and 11BCD0001). I'm currently have the measure that only calculates the result individually. However, I want to include the child company's financial data based on its parents' ownership % to each of its parent and calculate the result on the parents level. I have an example file that you can download and take a look at. Thank you so much for your assistance and time.

https://drive.google.com/file/d/1s7pZtXXyv5FaKVfd7DtShjCCI2xa9ZlC/view?usp=sharing

[MFelix · Accepted solution]

Hi @minhquankhong ,

Looking at the reduced information you have on your model the following measure works properly:

TOTAL AFBI + Owenrship percentage =
SUMX(VALUES('PL_Domestic Entitiies'[Entity Code1]),
[Total AFBI]
    + CALCULATE (
        [Total AFBI];
        FILTER (
            ALL ( 'PL_Domestic Entitiies' );
            'PL_Domestic Entitiies'[Entity Code1] IN VALUES ( 'Ownership %'[Entity Code1] )
        )
    )
        * LOOKUPVALUE (
            'Ownership %'[Ownership %];
            'Ownership %'[Owner]; SELECTEDVALUE ( 'PL_Domestic Entitiies'[Entity Code1] )
        )

Just did it for one of your measures, however if you have more relationships between companies and subsidiarie this may need some changes check the PBIX attach.

[minhquankhong]

Hi @MFelix 

Thank you so much. I think it works for my case (I'm still working on the data and plan to test it with massive data later). I have another question and hope that you can help. Is there a way to show the split amount of the child company (12CDE0001 - Company CDB) under its parents in the matrix table on the right hand side. I have attach the PBIX file for your reference. Thank you.

Example File 

[MFelix]

Hi  @minhquankhong ,

Believe that for this approach you will need to have a similar setup to the one in the blog below, the reference is to accounts but can be for companies.

https://www.thebiccountant.com/2016/07/21/easy-profit-loss-account-scheme-reports-power-bi-power-piv...

For this you need to add additional table wfor a one to many relationship