Forum Discussion

wagrezy's avatar
wagrezy
Helper I
6 years ago
Solved

Measure using columns from 2 different tables

Hi,   I have the following 2 tables - I would like to create a measure which multiply the units from Table1 with the relevant prices from Table2 (considering item and year).  I would like to do th...
Need Help
  • amitchandak's avatar
    amitchandak
    6 years ago

    Both are new column in table1

    //new column table 1 
price tab1 = maxx(filter(table2,table1[Item]=table2[Item] && table1[year]=table2[year]),table2[Price])

value = [price tab1]*[Units]
bfernandez's avatar
bfernandez
Resolver II
6 years ago

Hi wagrezy 

 

You need to first create the relationship on the Items:

 

Than create this measure:

Measure = SUM('Table1'[Units]) * SUM('Table2'[Price])
 
Let me know if this solves your problem.
 
If so, please mark this post as the solution to better assist others! 😁
wagrezy's avatar
wagrezy
Helper I
6 years ago

Hi,

Many thanks for your reply, that seems to work only if you create a relationship between the table that involve year and item, i.e. you have to dupplicate columns and merge them. 

Also, I noticed if you create a matrix, the total row would return a result much higher than the sum of the 3 rows.