Calculated Column Value According to The Type of Movement

[Syndicate_Admin]

Good morning community, I have the following problem, I have this data:

CODE	GUY	DATE	QTY	WITH
5269	II	20/5/2022	50	5,00
5269	COMP	21/5/2022	100	5,20
5269	VTA	22/5/2022	80	-
5269	COMP	23/5/2022	80	5,60
5269	AJE	24/5/2022	5	-
5269	VTA	25/5/2022	8	-
5269	VTA	26/5/2022	15	-

The Column "Code" represents an item of the warehouse,

The "Type" column is classified ("II" = Initial Inventory, "COMP" = Purchases, "VTA" = Sales", "AJE" = Adjustments)

Every time there are Types "II" and "COMP" the stock is increased (Suman), When there are "Types "VTA" and "AJE" there are stock reductions (Subtract).

It turns out that I must calculate the total value that each operation has, however when there is one of Type "II" or "COMP" it is simply multiply the Column "QTY" * "CU", now, when arriving at the Type "VTA" or "AJE" this must be the Average Value of the previous operations.

Currently I have other columns that calculate the "Accumulated QTY" by "Code" and the "Total Value" which is to multiply "QTY" * "CU".

CODE	GUY	DATE	QTY	WITH	QTY ACUMULADA	TOTAL VALUE
5269	II	20/5/2022	50	5,00	50	250,00
5269	COMP	21/5/2022	100	5,20	150	520,00
5269	VTA	22/5/2022	80	-	70	-
5269	COMP	23/5/2022	80	5,60	150	448,00
5269	AJE	24/5/2022	5	-	145	-
5269	VTA	25/5/2022	8	-	137	-
5269	VTA	26/5/2022	15	-	122	-

The problem arises when calculating the "CU" of the "VTA" and "AJE" Types since these must be the average CU of the previous operations; which must be calculated "Total Value" / "Accumulated QTY", and generates an average that is saved and accumulated, however the system throws me that there is a "Circular Dependency" for it.

Ideally, the result should be as follows:

CODE	GUY	DATE	QTY	WITH	QTY ACUMULADA	TOTAL VALUE	WITH PROM	TOTAL
5269	II	20/5/2022	50	5,00	50	250,00	5,0	250,0
5269	COMP	21/5/2022	100	5,20	150	520,00	5,1	770,0
5269	VTA	22/5/2022	80	-	70	-	5,1	359,3
5269	COMP	23/5/2022	80	5,60	150	448,00	5,4	807,3
5269	AJE	24/5/2022	5	-	145	-	5,4	780,4
5269	VTA	25/5/2022	8	-	137	-	5,4	737,4
5269	VTA	26/5/2022	15	-	122	-	5,4	656,6

What I want to validate is whether you can have a table with calculated columns that determine the value of the average inventory per transaction according to the different Imputs that are included.

I hope you can help me and grateful with any support.

Best regards!

[CarlosAugusto]

Estimado, te adjunto un ejemplo en Excel, de lo que mas o menos se requiere, de esta forma es mas comodo visualizar lo que necesito.

Saludos y agradecido por el apoyo.

[CarlosAugusto]

Estimado, adjunto ejemplo completo del calculo que se requiere.

CODIGO	TIPO	FECHA	QTY	CU	MOV	CU PROM	TOTAL OP	MOV ACUM	CU PROM	TOTAL ACUM
5269	II	20-05-2022	50,0	5,0	50	5,00	250,00	50	5,00	250,00
5269	COMP	21-05-2022	100,0	5,2	100	5,20	520,00	150	5,13	770,00
5269	VTA	22-05-2022	80,0	-	-80	5,13	-410,67	70	5,13	359,33
5269	COMP	23-05-2022	80,0	5,6	80	5,60	448,00	150	5,38	807,33
5269	AJE	24-05-2022	5,0	-	-5	5,38	-26,91	145	5,38	780,42
5269	VTA	25-05-2022	8,0	-	-8	5,38	-43,06	137	5,38	737,36
5269	VTA	26-05-2022	15,0	-	-15	5,38	-80,73	122	5,38	656,63

La idea es que cada vez que se genere un Tipo "COMP" ó "II" el calcule el valor promedio del inventario, mientra en el tipo "VTA" o "AJE" el tome el valor promedio de la fila anterior.

Agradecido por cualquier apoyo suministrado.

Saludos!

[CarlosAugusto]

Adjunto ejemplo en excel.

[Anonymous]

Hi @CarlosAugusto ,

Currently I can only do the following.

I have a few questions for you.

How are 5.0, 5.1 and 5.4 in the penultimate column calculated?

Why is the last column 2500 instead of 25000, is it divided by 10?

How is 3593 calculated? From your description, it seems to be about the average.

Best Regards,

Stephen Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.