multiply when a match between two tables

[mahra-in]

Hi

I have 

Table 1

Name	Year	Subject	Weightage
Alan	2016	CCA	0.1
Donal	2016	PPP	0.2
Dhoni	2017	SCA	0.15
Ram	2018	SSS	0.12
Siva	2018	SSS	0.18

Table 2

Name	Year	Spend
Alan	2016	154
Donal	2017	125
Dhoni	2017	676
Ram	2017	457
Donal	2016	458
Ram	2018	784

When there is a match between table 1 & 2 row wise, for eg: Alan 2016 in the 1st row of Table 1 match with Alan 2016 of Table 2 1st row. if matched the I want a calculated column in Table 2 by multiplying Spend with weightage

The result shall be in Table 2 as

Name	Year	Spend	Weighted Spend
Alan	2016	154	15.4
Donal	2017	125	0
Dhoni	2017	676	101.4
Ram	2017	457	0
Donal	2016	458	91.6
Ram	2018	784	94.08

Please help & thanks

[Phil_Seamark · Accepted solution]

Hi @mahra-in

Please add the following calculated column to your 'Table 2'

Weighted Spend = 
    'Table 2'[Spend] * 
     CALCULATE(
        MIN('Table 1'[Weightage]),
        FILTER(
            'Table 1',
            'Table 1'[Name] = EARLIER('Table 2'[Name]) &&
            'Table 1'[Year] = EARLIER('Table 2'[Year]) 
        )
        )

[mahra-in]

thank u