Forum Discussion
Measure (Multiply 2 columns from different tables) correct on lowest level but totals are too high
I am a beginner, just had a 2 days training and started with my first projects. Now I am facing the following problem. I want to multiply 2 columns from 2 different tables. The Measure "PlanBedarfStk" (#2 ist just another try) shows the correct value per item but the moment I select more than 1 item the totals are too high (calculation seems to happen on higher level).
Table "PlanStkVK" (only relevant columns)
| ArtikelNr(itemNr) | Month | PlanStk (planned pcs) |
| abc-12345 | 2021/01 | 5 |
| abc-12345 | 2021/02 | 0 |
| abc-12355 | 2021/01 | 3 |
Table "vw_aevo_Stückliste_mPreis" shows all the elements required to produce the items
| ArtikelNr | ElementNr | Menge (pcs) | MEK (price) | MengexMek (calculated column) |
| abc-12345 | bcd-12345 | 20 | 500 | 10.000 |
| abc-12345 | bxs-45623 | 25 | 10 | 250 |
| abc-12355 | xcv-56789 | 10 | 22 | 220 |
I guess the problem is the relation between the 2 tables (m:n).
Any ideas how i can solve this.
Finally I want to calculate the total costs of production (MengexMek) x (planned pieces).
But (Menge x planned pieces) already doesnt show correct figures if more than one item is selected.
Thanks a lot to the experts.
In general, (a + b + c) * (x + y + z) = a*x + a*y + a*z + b*x + b*y + b*z + c*x + c*y + c*z is not the same as a*x + b*y + c*z. I suspect you want the latter but your attempts are written like the former.
It's not completely clear to me how all your data tables are related so it's hard to say exactly, but you should be able to get the result you want using SUMX a bit like this:
_PlanBedarfStk = SUMX ( VALUES ( vw_aevo[ArtikelNr] ), [_PlanStk] * [_MEKxStk] )
2 Replies
In general, (a + b + c) * (x + y + z) = a*x + a*y + a*z + b*x + b*y + b*z + c*x + c*y + c*z is not the same as a*x + b*y + c*z. I suspect you want the latter but your attempts are written like the former.
It's not completely clear to me how all your data tables are related so it's hard to say exactly, but you should be able to get the result you want using SUMX a bit like this:
_PlanBedarfStk = SUMX ( VALUES ( vw_aevo[ArtikelNr] ), [_PlanStk] * [_MEKxStk] )Thank you soo much.
