Forum Discussion
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 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!
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
4 Replies
Anonymous Could you please post your expected output as well.
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
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
