Wrong result for sum of value of Purchase Order Items

Hi everyone,

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!

Greets
Jonas

Hi Jonas,

welcome to the Celonis Community!

The issue here is that a Join between Position / Vendor and the Activity table is executed. So every row in Position and Vendor is joined to all corresponding rows in the Activity table, and therefore, the rows in Position and Vendor are “multiplied”.

To fix this, I think we should first take a look at your data model. Is there a reason why you join the Vendors to the Activity table? Usually, it is joined to the Position table, because there is one vendor per position, and not per activity.

If you change your data model accordingly, you don’t have a join to the Activity table in your query anymore, and thus you should get the result you expect.

Best
David

Hi David,

thank you very much for the solution! I just had some false assumptions in my mind, but it works when joining the vendor and the position table. :slight_smile:

Best
Jonas