Help with Multiply columns by reference table

[Anonymous]

Hi, 

I need help with multiplying different columns. First, sum columns Sales and Pro then muliply it by the Growth from the other table when it match the name. 

I've done with the vlookup but now trying to use DAX. 

Name	Sale	Pro
Aby	15	15
John	814	16
Diana	52	19
Lia	222	19
Jim	65	19
Jim	215	20
Jim	22	33
Diana	215	22
Aby	64	23
Aby	512	2
John	65623	3
John	13265	26
John	326	98
Diana	51	28

Name	Growth A	Growth B
Aby	0.5	0.08
John	0.04	0.6
Diana	0.09	0.1
Lia	0.07	0.1
Jim	0.09	0.1

Thank you in advance!

[sokg · Accepted solution]

First you have to create a relationship as pic below.

then write these measures

Growth A Measure =
SUMX (
    'Sales Table';
    'Sales Table'[Sale] * 'Sales Table'[Pro]
        * RELATED ( 'Growth Table'[Growth A ] )
)

and

Growth B Measure =
SUMX (
    'Sales Table';
    'Sales Table'[Sale] * 'Sales Table'[Pro]
        * RELATED ( 'Growth Table'[Growth B] )
)

and the result is

[Anonymous]

Thank you, but I faced a little problem for some reason I was getting divided by two.  I just added ' *2 '  at the end. It works. 

[TomMartens]

Hey,

basically you can achieve something similar like VLOOKUP from Excel by using the DAX formula LOOKUPVALUE ...

and because (v1 + v2 + ...) * y is the same as v1 * y + v2 * y + ...

you can create a calculated column in table1

Assuming the 1st table is called table1 and the 2nd table is called table2 the DAX for this calculated column in table1 may look like this:

calc column in table1 = 
'table1'[Sale] * LOOKUPVALUE('table2'[Growth A], 'table2'[Name], 'table1'[Name])

To create a similar column in table2 the DAX gets a little more complex

calc column in table2 =
var currentName = 'table2'[Name]
return 
'table2'[Growth A]*
CALCULATE(
    SUM('table1'[Sale])
    ,FILTER(
        ALL('table1')
        ,'table1'[Name] = currentName
    )
)

Hopefully this is what you are looking for!

Regards,

Tom

[PattemManohar]

@Anonymous Could you please post your expected output as well.