Asset Allocation Roadmap for Investment Portfolio


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)

Hi @jsailar, we aim to consistently improve the topics being posted on the forum to help you in getting a strong solution faster. While waiting for a response, here are some tips so you can get the most out of the forum and other Enterprise DNA resources.

  • Use the forum search to discover if your query has been asked before by another member.

  • When posting a topic with formula make sure that it is correctly formatted to preformated text </>.

  • Use the proper category that best describes your topic

  • Provide as much context to a question as possible.

  • Include demo pbix file, images of the entire scenario you are dealing with, screenshot of the data model, details of how you want to visualize a result, and any other supporting links and details.

I also suggest that you check the forum guideline How To Use The Enterprise DNA Support Forum. Not adhering to it may sometimes cause delay in getting an answer.

Thanks to the contributors of this post. Due to the length by which this post has been active, we are tagging it as Solved. For further questions related to this post, please make a new thread.