Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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!
@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
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
109 | |
102 | |
84 | |
79 | |
70 |
User | Count |
---|---|
120 | |
110 | |
95 | |
82 | |
77 |