Combining weights from two tables to calculate a weighted percentage to show in a stacked bar chart

[jimrob]

Hi!

Sorry if this is already answered somewhere, I couldn't find it or didn't know what to search for.
I would like to aggregate data from one table using weights from another table in order to show the overall exposure. Below I have provided an example:

I am looking to acheive this desired result (manually created in excel to demonstrate):

I have two tables - one of Portfolios, which each hold mulitple securities, with Weights that sum to 100 for each Portfolio. And one of Securities, which each have one or more geographical exposures, which sum to 100. (In Power BI I have created a many to many relationship between the tables by Security ID.)

A table of portfolios with the weight of each security:

Portfolio ID	Security ID	Weight
123456	Fund A	20%
123456	Fund B	40%
123456	Fund C	40%
987654	Fund A	10%
987654	Fund C	50%
987654	Fund D	40%

A table of securities with the weight of each geography:

Security ID	Geography	Weight
Fund A	USA	50%
Fund A	Europe	20%
Fund A	UK	10%
Fund A	Asia	20%
Fund B	USA	90%
Fund B	UK	10%
Fund C	Europe	80%
Fund C	UK	20%
Fund D	Asia	100%

What type of relationship or measure or changes to the way the data is stored do I need in order to achieve the above?

Thanks in advance!

[amitchandak]

@jimrob , Merge these in power query and multiple their Weight and create a new column 

Merge: https://radacad.com/append-vs-merge-in-power-bi-and-power-query

[jimrob]

Thanks @amitchandak !

I presume you mean a new column for each geography? Unfortunately in the real data that would be many many columns.

I was hoping there is a way to establish the relationship more dynamically.