Create table displaying weighted averages of various data points for two accounts

[Anonymous]

Given the first table with stocks, portfolio weights, and stock statistics, how can I use Power BI to populate and display the second table? I'm new to this, and am able to create a New Measure that shows Portfolio 1's dividend yield. Would I need to create a new measure for each data point for each portfolio, or is there a shorter way? Thanks in advance!

Stock	Portfolio 1 Weight	Portfolio 2 Weight	Price	Dividend Yield	EV	Basic EPS	P/E	Debt/Assets	ROE
TSLA US EQUITY	15.0%	25.0%	162.27	0.00	0.50	4.73	72.98	8.98	24.22
MSFT US EQUITY	25.0%	40.0%	408.35	0.73	3.06	9.72	36.94	19.28	39.17
PRU US EQUITY	12.5%	4.0%	112.39	4.63	0.05	6.76	9.72	3.59	8.25
V US EQUITY	20.0%	20.0%	276.20	0.75	0.57	10.68	31.33	23.18	48.67
T US EQUITY	10.0%	1.0%	16.91	6.57	0.28	1.97	7.28	38.92	13.15
CSCO US EQUITY	17.5%	10.0%	48.25	3.32	0.18	3.08	14.08	9.24	30.65

	Portfolio 1	Portfolio 2
Price
Dividend Yield
EV
Basic EPS
P/E
Debt/Assets
ROE

[OwenAuger · Accepted solution]

Hi @Anonymous 

Here's how I would set this up (PBIX attached).

1. Set up model as shown:

Portfolio

Stock

Metric

Portfolio Weight

Stock Metric

2. Create these measures

# Metrics = 
COUNTROWS ( Metric )

# Portfolios = 
COUNTROWS ( Portfolio )

Metric Value = 
-- Ensure only aggregating one metric
IF (
    [# Metrics] = 1,
    AVERAGE ( 'Stock Metric'[Value] )
)

Weighted Metric Value = 
-- Ensure only aggregating one portfolio
IF (
    [# Portfolios] = 1,
    SUMX (
        'Portfolio Weight',
        [Metric Value] * 'Portfolio Weight'[Weight]
    )
)

3. Create test matrix visual:

• Rows: Metric[Metric]
• Columns: Portfolio[Portfolio]
• Values: [Weighted Metric Value]

This is a start anyway. You may well want to extend with measures that aggregate Portfolios or perform other calculations.

Does that help?

Regards,

[Anonymous]

Fantastic. Thanks so much!