Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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!
Solved! Go to 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
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
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.
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
@Anonymous Could you please post your expected output as well.
Proud to be a PBI Community Champion
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
112 | |
97 | |
85 | |
67 | |
59 |
User | Count |
---|---|
150 | |
120 | |
100 | |
87 | |
68 |