Calculating total margin after increase per position

[avantime]

Hi,

I can`t get a solution for this problem, maybe you can help me. This has to be done in an expression, not at script level, the variables are changed using input boxes inside the application.

PRODUCT	PRODUCT VARIANT	Revenue	COGS	Margin	Margin + Factor	COGS after margin increase		Variables
A	A1	100	50	50.00%	52.00%	48		Factor1	2.00%
A	A2	312	32	89.74%	91.74%	25.76		Factor1	2.00%
A	A3	423	56	86.76%	88.76%	47.54		Factor1	2.00%
A	A4	332	44	86.75%	88.75%	37.36		Factor1	2.00%
B	B1	250	87	65.20%	70.20%	74.5		Factor2	5.00%
B	B2	228	54	76.32%	81.32%	42.6		Factor2	5.00%
C		225	25	88.89%	95.89%	9.25		Factor3	7.00%
D		325	56	82.77%	84.77%	49.5		Factor4	2.00%
E		120	44	63.33%	67.33%	39.2		Factor5	4.00%
		2315	448	80.65%	?????????????	?????????????

How would you calculate the total increased margin?

Thank you

[avantime]

Thanks Jack, I know what margin is and how to calculate it, my problem is finding the expression to calculate total margin after increase (for forecasting purposes).

Right now I have something like:

((Sum(Revenue)-Sum(COGS))/Sum(Revenue)+

avg(Aggr(Sum(DISTINCT
IF(Product = 'A, Factor1/100,
IF(Product = 'B', Factor2/100,
IF(Product = 'C', Factor3/100,
IF(Product = 'D', Factor4/100,
IF(Product = 'E', Factor5/100))))))
,PRODUCT))

While the calculation is correct for each product, I do not have a total because of the part of the script involving factors.

I also tried doing an AGGR with the entire script inside of it but I didn`t get the desired result.

[avantime]

no one?

[avantime · Accepted solution]

I found the solution myself after all.

I changed the formula above into:

(Sum(Revenue) - Sum(Cost) +

Sum(Aggr(Sum(Revenue) *

IF(Product = 'A, Factor1/100,
IF(Product = 'B', Factor2/100,
IF(Product = 'C', Factor3/100,
IF(Product = 'D', Factor4/100,
IF(Product = 'E', Factor5/100),PRODUCT))

/ Sum(Revenue)