cancel
Showing results for
Did you mean:
Highlighted
Helper I

## multiply when a match between two tables

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

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Microsoft

## Re: multiply when a match between two tables

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

Proud to be a Datanaut!

2 REPLIES 2
Highlighted
Microsoft

## Re: multiply when a match between two tables

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

Proud to be a Datanaut!

Highlighted
Helper I

## Re: multiply when a match between two tables

thank u

Announcements

#### Power Platform Community Conference

Check out the on demand sessions that are available now!

#### Experience what’s next for Power BI

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

#### Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021