Forum Discussion
Help with a formula - multiplying across two tables
Hi all
I am trying to work out how to write this forumla. I feel like it's actually really simple and my brain is just not working today
I have two tables:
Table 1 contains Product code, and Quantity
Table 2 contains Product Code, Country and % split
They are each joined to a Dim Product Code table in the middle of the two.
I want to be able to multiple the quantity by the % split
Here is an example of the desired output:
| SKU | Total Quantity | Italy % | Italy QTY | ||
| Product 1 | 336 | 0.0094031 | 3.1594416 | ||
| Product 1 | 605 | 0.0094031 | 5.6888755 | ||
| Product 2 | 328 | 0.0651938 | 21.3835664 | ||
| Product 2 | 512 | 0.0651938 | 33.3792256 | ||
| Product 2 | 1 | 0.0651938 | 0.0651938 | ||
| Product 2 | 157 | 0.0651938 | 10.2354266 |
Total sum = 73.91 for Italy for those two products.
what I get with my formula is:
| Country | PercentSplit | SumPercentSplit | Quantity | |||
| Italy | 7.46% | 7.46% | 144.5687922 |
And I KNOW what I'm doing wrong. I'm clearly summing the % of both products and applying it to the total of both products, but I'm not sure how to fix it.
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])
4 Replies
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])
