SUMPRODUCT Measure Using Fields from Same Table

[dashmarley11]

I'm trying to create a Measure that calculates the SUMPRODUCT using fields from the same table.  

Using an EXCEL layout as seen below, the formula used to calculate the ".024%" in D1 is:  =SUMPRODUCT(A3:A26, B3:B26, D3:D26) / SUMPROUCT(A3:A26, D3:D26)

A	B	C	D
		0.19%	0.24%
Term	Rate	12MoPmts	24MoPmts
1	0.0017	81440.81512	39704.72307
2	0.0017	81780.15185	39870.15941
3	0.0017	82120.90248	40036.28508
4	0.0017	82463.07291	40203.10293
5	0.0017	82806.66905	40370.61586
6	0.0017	83151.69684	40538.82676
7	0.001766667	83498.16224	40707.73854
8	0.001833333	83846.07125	40877.35412
9	0.0019	84195.42988	41047.67642
10	0.001966667	84546.24417	41218.70841
11	0.002033333	84898.52019	41390.45303
12	0.0021	85252.26402	41562.91325
13	0.002175	0	41736.09205
14	0.00224375	0	41909.99244
15	0.002319375	0	42084.61741
16	0.002395	0	42259.96998
17	0.002470625	0	42436.05319
18	0.00254625	0	42612.87008
19	0.002621875	0	42790.4237
20	0.0026975	0	42968.71713
21	0.002773125	0	43147.75345
22	0.00284875	0	43327.53576
23	0.002924375	0	43508.06716
24	0.003	0	43689.35077

I'm currently using the following formula in PowerBI but I'm getting different/incorrect results:

=SUMX('Table1', 'Table1'[Rate] * LOOKUPVALUE('Table1'[24MoPmts], 'Table1'[Term], 'Table1'[Term])) / SUMX('Table1', LOOKUPVALUE('Table1'[24MoPmts], 'Table1'[Term], 'Table1'[Term]))

This formula returns ".22%", but it should be ".24%".   

[DataNinja777 · Accepted solution]

Hi @dashmarley11 ,

In that case, I suppose you don't need to include that 'Term' column in your sumproduct calculation either, and when that is removed, Power BI sumx calculation result and sumproduct calculation results are the same, although I would write dax formula in the following manner which is simpler.  

Best regards,

[DataNinja777]

Hi @dashmarley11 

In your Excel formula, term is also included, but I would have throught that the overall rate calculation should be based on the amount and the rates only. I'd appreciate it if you could let me know why the indexing column of "Term" should also be included in the calculation.  

Best regards,

[dashmarley11]

@DataNinja777  I'm not sure actually, that's just the exact formula that is being used in the Excel calculation but if it's not necessary to get to the correct answer then I suppose it's not applicable.