Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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)
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.
no one?
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)