Forum Discussion
Portfolio Model
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%
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.
2 Replies
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
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.
