Hello,
I am looking to create an asset allocation ‘roadmap’ for a portfolio of securities (investments). This means, I’d like a visualization of what the client currently holds in their portfolio (which can be made up of many accounts) vs. what we would propose they hold (The ‘Target allocation’) based on the appropriate model referenced in the Model Portfolios Table. The visualization ( I am imagining a table) would need to include The securities currently owned, those that are not currently owned and the difference between the current and the recommended target holdings.
In my mind I would think that you start with a virtual table combining the Current assets table & the Model portfolios table. I’m not sure of the best way to do this. I have been thinking of the ** Model portfolios table** almost like a budget table which is typically disconnected from the fact table but I can get the table to work by connecting it to the Securities Lookup. Not sure if this is the most efficient way.
You could then apply the Value Measures I’ve built:
- Current Market Value
- Current Weighting
- Gains & Losses
Measures I would need:
-
Target Weighting: This should return the Sum of the % weighting shown in the model portfolios based on the election made in the Model portfolio slicer. I’ve created the Model Portfolio_Lookup table to act as the slicer as well as the measure for portfolio selected but I am unsure how to construct the Target Weighting from there. The goal here is, I would like to be able to elect to change the model portfolio being analyzed and the account(s) being analyzed with the slicer.
-
Allocation Difference. This is the Difference between the Current Weighting and the Target Weighting i.e. the weighting of the selected model portfolio.
Where this gets complicated is that I would like for the user to be able to elect to either sell or not sell a specific security and when that election is made, the Asset Allocation reflects the users decision. A decision to sell a specific security would have a few results. It would lower the current weighting depending on the type of security (Equity, fixed income etc…) it would increase the cash weighting by the market value of the security sold and if the security has a taxable gain or loss that would be summed as well.
I’m not certain that Power BI can do something like this but if it can, I’m sure someone here knows how.
Attached are the files I have so far. I also attached an excel file for reference that illustrates how I’ve been doing this in excel by hand. The securities listed there are different than those in the Model Portfolio Table but the concept is the same.
I hope this is enough to go off of. Thanks so much for your consideration on this.
eDNA - Securities Roadmap Dashboard.pbix (397.5 KB)
eDNA Excel Roadmap template.xlsx (23.8 KB)