Forum Discussion

mahra-in's avatar
mahra-in
Helper II
8 years ago
Solved

multiply when a match between two tables

Hi

 

I have 

 

Table 1

 

NameYearSubjectWeightage
Alan2016CCA0.1
Donal2016PPP0.2
Dhoni2017SCA0.15
Ram2018SSS0.12
Siva2018SSS0.18

 

Table 2

NameYearSpend
Alan2016154
Donal2017125
Dhoni2017676
Ram2017457
Donal2016458
Ram2018

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

 

NameYearSpendWeighted Spend 
Alan201615415.4
Donal2017125 0
Dhoni2017676101.4
Ram2017457 0
Donal201645891.6
Ram201878494.08

 

Please help & thanks

Need Help
  • Phil_Seamark's avatar
    Phil_Seamark
    8 years ago

    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]) 
        )
        )

2 Replies

  • Phil_Seamark's avatar
    Phil_Seamark
    Microsoft Employee
    8 years ago

    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]) 
        )
        )