TESTSUMSUB

nihou

Hello, I am having trouble sorting and subtotaling based on types with various difference?

source
P#	NAME	Product	Code	Dcode	PurchCode	C % M	P % M	Vol	Val	Type
500	Sam	Grapes	UNIT	SP	Smith	0.5	0.5	0.5	0.5	C
500	Sam	Grapes	MV	SP	Smith	0.5	0.5	0.3	0.3	P
500	Sam	Grapes	MV	SP	Smith	0.5	0.5	0.2	0.2	P
500	Sam	Grapes	UNIT	SR	Smith	0.5	0.5	0.5	0.5	C
500	Sam	Grapes	MV	SR	Smith	0.5	0.5	0.3	0.3	P
500	Sam	Grapes	MV	SR	Smith	0.5	0.5	0.2	0.2	P
500	Sam	Pear	UNIT	SR	Smith	0.5	0.5	0.75	0.75	C
500	Sam	Pear	MV	SR	Smith	0.5	0.5	0.21	0.21	P
500	Sam	Pear	MV	SR	Smith	0.5	0.5	0.04	0.04	P
desired outcome
P#	NAME	Product	Code	Dcode	PurchCode	C % M	P % M	Vol	Val	Type	VAR1	VAR2	RESULT
500	Sam	Grapes	UNIT	SP	Smith	0.5		0.5	0.5	C	0	0
500	Sam	Grapes	MV	SP	Smith		0.5	0.5	0.5	P	0	0
500	Sam	Grapes	UNIT	SR	Smith	0.5		0.5	0.5	C	0	0
500	Sam	Grapes	MV	SR	Smith		0.5	0.5	0.5	P	0	0
500	Sam	Pear	UNIT	SR	Smith	0.5		0.75	0.75	C	-0.25	-0.25	FLAG
500	Sam	Pear	MV	SR	Smith		0.5	0.25	0.25	P	0.25	0.25	FLAG

TEST SUM2.png

TEST SUM1.png

TEST SUM SUB.xlsx

ConnorK

Hi @nihou,

Please see the attached workflow. The major part of the solution is using the summarize tool create your groupings then calculating var1 & 2 with a formula tool. The output order is a little bit different but gets the same results.

I hope this helps!

Subtotal Example.yxmd