New Table that Calculates SUMPRODUCT from two separate tables

[dashmarley11]

I'm trying to create a new table that calculates a SUMPRODUCT using values from 2 separate tables (T1 and T2).

T1

Term	FTP Rate
1	.0549
2	.0548
3	.0547
4	.0520
5	.0511
6	.0501

T2

Term	Pmt-3	Pmt-6
1	81,441	39,705
2	81,780	39,870
3	82,121	40,036
4		40,203
5		40,371
6		40,359

NEWTable

Pmt-3Rt	SUMPRODUCT('T1' A1:A6, 'T1' B1:B6, 'T2' B1:B6)/SUMPRODUCT('T1' A1:A6, 'T2' B1:B6)
Pmt-6Rt	SUMPRODUCT('T1' A1:A6, 'T1' B1:B6, 'T2' C1:C6)/SUMPRODUCT('T1' A1:A6, 'T2' C1:C6)

[v-yilong-msft · Accepted solution]

Hi @dashmarley11 ,

It seems that Power BI does not directly support the SUMPRODUCT function as it is known in Excel. But I think you can use LOOKUPVALUE function.

I create two tables as you mentioned.

Then I create a measure and here is the DAX code.

SUMPRODUCT Measure =
SUMX ( T1, T1[FTP Rate] * LOOKUPVALUE ( T2[Pmt-3], T2[Term], T1[Term] ) )
    / SUMX ( T1, LOOKUPVALUE ( T2[Pmt-3], T2[Term], T1[Term] ) )

Finally you will get what you want.

Best Regards

Yilong Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Greg_Deckler]

@dashmarley11 Try:

SUMPRODUCT = 
    VAR __Table = 
        ADDCOLUMNS(
            'Table1',
            "Value",[Value1] * RELATED(Table2[Value2])
        )
RETURN
    SUMX(__Table,[Value])

Comes from here: S Excel to DAX Translation - Microsoft Fabric Community