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]
v-gizhi-msft's avatar
v-gizhi-msft
Community Support
6 years ago

Hi,

 

Please try this measure without creating any relationships:

Measure = CALCULATE(SUMX(Table1,Table1[Units]*CALCULATE(SUM('Table2'[Price]),FILTER('Table2','Table2'[Item] in FILTERS('Table1'[Item])))))

Choose [Item] from table1 and this measure as a table visual, it shows:

 

 

Best Regards,

Giotto Zhi

wagrezy's avatar
wagrezy
Helper I
6 years ago

Hi,

Many thanks for your reply.

That also applies the calculation to 2019 as there is no relationship on table for years. Again the total rows seems to be much higher than the sum of the 3 rows.