Portfolio Model

[JHob]

Hi

I am modeling performance of stocks in a portfolio to their benchmark.  I have a master table with individual stocks and a portfolio table with the percentage of each individual stock.

I need to make comparisons of the portfolio weightings compared to the benchmark portfolioi weightings.

Should I create a separate table for the benchmarks or should I combine in the portfoliot table.  For example portfolio table if combined will be

PORTFOLIO1 Stock1 x%

PORTFOLIO1 Stock 2 y%

PORTFOLIO2 Stock1 xx%

PORTFOLIO2 Stock 2 yy%

PORTFOLIO2 Stock 3 zz%

BENCHMARK2 Stock 2 a%

BENCHMARK2 Stock 3 b%

[andre · Accepted solution]

You need to have a Portfolio Table and Portfolio Details table to capture portfolio information, then you will need to have a benchmark and benchmark details table for Benchmark and then you need to find a way to link your Porftolio record with a corresponding benchmark record so you know which porfolios use what for benchmark.

this would be easiest, if i understand your problem correctly.. you could potentially have it all in two tables, summary and detail, but then you will need to have a flag in summary table to specify whehter it's a portfolio or benchmarck, and then add another column for benchmark ID, so you can link portfoloio data with benchmark data.  but that woud make DAX to do analysis a little bit more complicated.

[mahoneypat]

I would combine it as you've shown.  That way, assuming you have a 1:Many from Stocks to Portfolios, you can have a measure like

Portfolio Value = SUMX(Portfolio, Portfolio[Percent] * RELATED(Stocks[Value]))

or something like that to compare portfolios.

If this works for you, please mark it as the solution.  Kudos are appreciated too.  Please let me know if not.

Regards,

Pat