Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

Help with Multiply columns by reference table

  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...
General Comment
Interesting Links
Tips and Tricks
Tutorial Requests
  • sokg's avatar
    sokg
    7 years ago

    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

     

TomMartens's avatar
TomMartens
Super User
7 years ago

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