cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Helper I
Helper I

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

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Microsoft
Microsoft

Re: multiply when a match between two tables

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

image.png


To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

View solution in original post

2 REPLIES 2
Highlighted
Microsoft
Microsoft

Re: multiply when a match between two tables

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

image.png


To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

View solution in original post

Highlighted
Helper I
Helper I

Re: multiply when a match between two tables

thank u

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

Upcoming Events

Experience what’s next for Power BI

See the latest Power BI innovations, updates, and demos from the Microsoft Business Applications Launch Event.

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

Mark your calendars and join us for our next Power BI Dev Camp!.

Top Solution Authors
Top Kudoed Authors