I am analyisng a P2P process and in my data model I have an activity table, which is linked to the tables “Vendor” and “Position” via a vendor-/position-number. The “Vendor” table contains master data for all vendors which I buy stuff from, i.e. one row for each vendor. And the “Position” table contains master data regarding the purchas-positions I buy, i.e. one row for each purchase-position.
So there is a 1:N relationship between the vendor- / position-table (1) and the activity table (N).
Now I would like to display the amount I pay for all positions bought at one vendor. But if I sum up the values for all positions belonging to a certain vendor i do not get the desired value, but I get the sum for all positions for the vendor multiplied with all activities which have been carried out for this position.
E.g. if I have 2 items I bought from a vendor, each costs 10€, the formula does not calculate “20”, but 180, as for both items 9 activites have been carried out. (10 * 9 * 2 = 180).
Does anybody know a solution to this problem?
Thanks a lot already in advance!