Benchmark Weighted Average

[bslh88]

Hi everyone, 

Appreciate any help I can get here. I have three tables: 

Table 1 lists the details of funds that I invest in and the value of each issuer in the fund. 

FundDetail
Date	Fund	Issuer	Value
31/12/2022	Fund_A	Issuer_A	120
31/12/2022	Fund_A	Issuer_B	120
31/12/2022	Fund_A	Issuer_C	120
31/12/2022	Fund_A	Issuer_D	120
31/12/2022	Fund_A	Issuer_E	120
31/12/2022	Fund_B	Issuer_C	60
31/12/2022	Fund_B	Issuer_D	60
31/12/2022	Fund_B	Issuer_E	60
31/12/2022	Fund_B	Issuer_F	60
31/12/2022	Fund_B	Issuer_G	60
31/12/2022	Fund_C	Issuer_A	200
31/12/2022	Fund_C	Issuer_B	200
31/12/2022	Fund_C	Issuer_C	200
31/12/2022	Fund_C	Issuer_F	200
31/12/2022	Fund_C	Issuer_G	200
31/12/2023	Fund_A	Issuer_A	150
31/12/2023	Fund_A	Issuer_B	150
31/12/2023	Fund_A	Issuer_C	150
31/12/2023	Fund_A	Issuer_D	150
31/12/2023	Fund_A	Issuer_E	150
31/12/2023	Fund_B	Issuer_C	90
31/12/2023	Fund_B	Issuer_D	90
31/12/2023	Fund_B	Issuer_E	90
31/12/2023	Fund_B	Issuer_F	90
31/12/2023	Fund_B	Issuer_G	90
31/12/2023	Fund_C	Issuer_A	400
31/12/2023	Fund_C	Issuer_B	400
31/12/2023	Fund_C	Issuer_C	400
31/12/2023	Fund_C	Issuer_F	400
31/12/2023	Fund_C	Issuer_G	400

Table 2 lists the details of the index that the funds are benchmarked against and the weight of each issuer in the benchmarks. 

BenchmarkDetail
Date	Benchmark	Issuer	Weight
31/12/2022	BM_A	Issuer_A	16.7%
31/12/2022	BM_A	Issuer_B	16.7%
31/12/2022	BM_A	Issuer_C	16.7%
31/12/2022	BM_A	Issuer_D	16.7%
31/12/2022	BM_A	Issuer_E	16.7%
31/12/2022	BM_A	Issuer_F	16.7%
31/12/2022	BM_B	Issuer_D	20.0%
31/12/2022	BM_B	Issuer_E	20.0%
31/12/2022	BM_B	Issuer_F	20.0%
31/12/2022	BM_B	Issuer_G	20.0%
31/12/2022	BM_B	Issuer_H	20.0%
31/12/2023	BM_A	Issuer_A	16.7%
31/12/2023	BM_A	Issuer_B	16.7%
31/12/2023	BM_A	Issuer_C	16.7%
31/12/2023	BM_A	Issuer_D	16.7%
31/12/2023	BM_A	Issuer_E	16.7%
31/12/2023	BM_A	Issuer_F	16.7%
31/12/2023	BM_B	Issuer_D	20.0%
31/12/2023	BM_B	Issuer_E	20.0%
31/12/2023	BM_B	Issuer_F	20.0%
31/12/2023	BM_B	Issuer_G	20.0%
31/12/2023	BM_B	Issuer_H	20.0%

Table 3 lists maps the funds to the benchmark it tracks. 

FundBenchmarkMap
Fund	Benchmark
Fund_A	BM_A
Fund_B	BM_A
Fund_C	BM_B

I'm trying to calculate the weight and value of each issuer in a blended benchmark. The value of securities in the blended benchmark are calculated by multiplying the weight in the standalone benchmarks with the total value of the securities in the fund that it is benchmarked against. The blended weight is the value of these securities against the total value of all the selected funds. In excel, this will look like the table below: 

Date	Fund	Benchmark	Issuer	Weight	TotalValueofFund	Value in Benchmark	BlendedWeight
31/12/2022	Fund_A	BM_A	Issuer_A	16.7%	600	100	5.3%
31/12/2022	Fund_A	BM_A	Issuer_B	16.7%	600	100	5.3%
31/12/2022	Fund_A	BM_A	Issuer_C	16.7%	600	100	5.3%
31/12/2022	Fund_A	BM_A	Issuer_D	16.7%	600	100	5.3%
31/12/2022	Fund_A	BM_A	Issuer_E	16.7%	600	100	5.3%
31/12/2022	Fund_A	BM_A	Issuer_F	16.7%	600	100	5.3%
31/12/2022	Fund_B	BM_A	Issuer_A	16.7%	300	50	2.6%
31/12/2022	Fund_B	BM_A	Issuer_B	16.7%	300	50	2.6%
31/12/2022	Fund_B	BM_A	Issuer_C	16.7%	300	50	2.6%
31/12/2022	Fund_B	BM_A	Issuer_D	16.7%	300	50	2.6%
31/12/2022	Fund_B	BM_A	Issuer_E	16.7%	300	50	2.6%
31/12/2022	Fund_B	BM_A	Issuer_F	16.7%	300	50	2.6%
31/12/2022	Fund_C	BM_B	Issuer_D	20.0%	1000	200	10.5%
31/12/2022	Fund_C	BM_B	Issuer_E	20.0%	1000	200	10.5%
31/12/2022	Fund_C	BM_B	Issuer_F	20.0%	1000	200	10.5%
31/12/2022	Fund_C	BM_B	Issuer_G	20.0%	1000	200	10.5%
31/12/2022	Fund_C	BM_B	Issuer_H	20.0%	1000	200	10.5%

I'm trying to find a solution that works even when I remove the Fund and Index column filters. 

Date	Issuer	Value in Benchmark	BlendedWeight
31/12/2022	Issuer_A	150	7.9%
31/12/2022	Issuer_B	150	7.9%
31/12/2022	Issuer_C	150	7.9%
31/12/2022	Issuer_D	350	18.4%
31/12/2022	Issuer_E	350	18.4%
31/12/2022	Issuer_F	350	18.4%
31/12/2022	Issuer_G	200	10.5%
31/12/2022	Issuer_H	200	10.5%

Getting to the last table is where I am stuck. Also bear in mind there are issuers in the Funds that are not in the index that it is benchmarked against and vice versa. 

Thanks

[lbendlin]

You lost me on the blended weights, and I also get slightly different results for the third table.

Attached is how far I got.

[bslh88]

Thanks @lbendlin 

I should have been clearer in my description but you are on the right track. But as you can see, the differences occur when there are mismatches between Issuer(s) in the Benchmark and the Issuer(s) in the Fund. 

My current, not so elegant solution is by creating another query which merges all the necessary columns. I'd settle for it, but my original data is much longer than this, so the performance lag is considerable, not to mention I'm not able to display certain visualisation due to resource maxing out. 

I'm hoping for a dax solution that have the same effect as creating the Merged table in the pbix attached without having to duplicate the table and use too many calculated columns. 

Benchmark Weighted Average (2)