topic Re: Create table displaying weighted averages of various data points for two accounts in DAX Commands and Tips

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

Anonymous — Wed, 24 Apr 2024 19:42:13 GMT

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

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

OwenAuger — Wed, 24 Apr 2024 20:54:38 GMT

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,

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

Anonymous — Wed, 24 Apr 2024 22:19:52 GMT

Fantastic. Thanks so much!