Forum Discussion
Help with a formula - multiplying across two tables
dapperscavenger , Try measure likes
calculate( maxx(filter(Table1, table1[Product code] = max(table2[Product code]) ),Sum(Table[Quantity])) * max(table2[% split]) ,values(Table2[Product]),values(Table2[Country]))
or
sumx(summarize( Table2, Table2[Product],Table2[Country],"_1", maxx(filter(Table1, table1[Product code] = max(table2[Product code]) ),Sum(Table[Quantity])) ,"_2",max(table2[% split])), [_1]*[_2])
dapperscavenger , one way was to merge these tables in Power query and then you could have done the calculation in a column.
or have a new column in table 2 like
new column in table 2 = maxx(filter(Table1, table1[Product code] = table2[Product code] ),[Quantity]) *table2[% split]
Ah, I tried this in M but it made my table 1.3 million rows, bloated my file size, and the refresh was slow, so I was wondering if DAX was a better solution.
Couldn't get the second approach to work - seems to need a measure instead of table2[product code]
dapperscavenger , Try measure likes
calculate( maxx(filter(Table1, table1[Product code] = max(table2[Product code]) ),Sum(Table[Quantity])) * max(table2[% split]) ,values(Table2[Product]),values(Table2[Country]))
or
sumx(summarize( Table2, Table2[Product],Table2[Country],"_1", maxx(filter(Table1, table1[Product code] = max(table2[Product code]) ),Sum(Table[Quantity])) ,"_2",max(table2[% split])), [_1]*[_2])Yes, the second measure works, thank you!
