How to calculate and distribute JV percentage based on entities allocation using DAX

[eddiensk]

Hi Guru,

i have a scenario that i need help. We have a table that stores the distribution percentages from JV entities to our primary entities as below. In below example, assuming the measures to be distributed is Costs ($). 

for Entity Company C, the total cost = Costs of company C + Costs of company E x 35% + costs of company F x 32%

Target Entity	Source Entities	Ownership % of Source Entity
Company A	Company B	50
Company C	Company E	35
Company C	Company F	32
Company D	Company E	30
Company D	Company F	20

assuming the transaction table store are as below. The distributed costs are calcualted based on formula and percentages of distribution to get the JV costs and then the total costs

Entity	Own Cost ($)	Distributed JV Costs	Total Costs
Company B	1200	0	1200
Company A	2000	600	2600
Company E	3500	0	3500
Company F	1500	0	1500
Company C	3000	1705	4705
Company D	5000	1350	6350

example above shows

entity Company A = $2000 (costs of Entity Company A) + $1200 * 0.5 (Costs of JV Company B) = $ 2600

anyone can help with me this please. Been cracking for a week ... guess it is too challenging for a new bie like me 😞

Cheers
Ed

[v-jingzhang · Accepted solution]

Hi @eddiensk 

You can use Power Query Editor to calculate the Distributed JV Costs and Total Costs. First import two tables into Power BI and click Transform data to launch Power Query Editor. 

I transform the transaction table with Merge Queries feature twice and Group by feature once. Also add some custom columns. Merge Queries feature is very useful when you want to bring data from another table (or the table itself) by one or more matching columns. Group by feature is very helpful when you want to calculate the aggregated values by some columns (groups). You can download the attached pbix file at bottom to see detailed transformation steps. Let me know if you have any questions. 

Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it.

[eddiensk]

Thanks Jing, i have tried your suggested method and it works. However, i have another measure that i need to distribute, which is count of invoices. This count of invoice is currently implemented as measure based on CountX (table, table(accounting document) & table(year) & table(company code).

I couldn't get this work using the power query to distribute the counts as the fact table has granularity deeper than the 3 fields above. I need those other fields for ensuring all filters are considered.

The other dilemma i have is the fact table is large, so using the power query seems to be slow and need to introduce a few other tables due to merging as new table. I have incremental refresh for the original fact table, do i need to implement the incremental refresh for the merged table as well?

thanks

eddie

[eddiensk]

thanks Jing,

appreciate your response. in my real scenario it is more complicated than that. 

- the Own Costs is actually a calculated amount itself based on multiple cost fields, calculation and filters and the filters needs to be preserved as well when doing the distribution. This calculated "Own Costs" is defined as one of the measure. 

Is there another way to do the distribution based on the calculated measure?

thanks heaps

[eddiensk]

 any help very much appreciated...