Hi Greg,
I have posted my code below. This is what I am trying to achieve. let me know if this helps.
Target Stock =
Var axservicelevel = IFERROR(NORM.S.INV(MAX(Parameters[Service Level AX])/100),0)
Var ayservicelevel = IFERROR(NORM.S.INV(MAX(Parameters[Service Level AY])/100),0)
Var azservicelevel = IFERROR(NORM.S.INV(MAX(Parameters[Service Level AZ])/100),0)
Var bxservicelevel = IFERROR(NORM.S.INV(MAX(Parameters[Service Level BX])/100),0)
Var byservicelevel = IFERROR(NORM.S.INV(MAX(Parameters[Service Level BY])/100),0)
Var bzservicelevel = IFERROR(NORM.S.INV(MAX(Parameters[Service Level BZ])/100),0)
Var cxservicelevel = IFERROR(NORM.S.INV(MAX(Parameters[Service Level CX])/100),0)
Var cyservicelevel = IFERROR(NORM.S.INV(MAX(Parameters[Service Level CY])/100),0)
Var czservicelevel = IFERROR(NORM.S.INV(MAX(Parameters[Service Level CZ])/100),0)
Var boxtypeselection = VALUES('9 Box Type Selection'[Value])
Var targettypeselection = VALUES('Target Stock Selection'[Value])
var ax =
CALCULATE(
axservicelevel
*
SQRT(
DIVIDE(IF(OR(boxtypeselection=1,boxtypeselection=2),
AVERAGE('Lead Time Data'[Lead_Time]),
AVERAGE('Manual Lead Time'[Lead_Time])),30,0)
*
IF(OR(boxtypeselection=1,boxtypeselection=3),
POWER(sum(XYZ_Sales[Revenue_Monthly_Std]),2),
POWER(sum(XYZ_Sales[Quantity_Monthly_Std]),2))
+
IF(targettypeselection=2,
POWER(AVERAGE('Lead Time Data'[Lead_Time_Deviation])
*
IF(OR(boxtypeselection=1,boxtypeselection=3),
SUM(XYZ_Sales[Revenue_Monthly_Avg]),
SUM(XYZ_Sales[Quantity_Monthly_Avg]))
,2),0)
)
+
DIVIDE(
DIVIDE(IF(OR(boxtypeselection=1,boxtypeselection=2),
AVERAGE('Lead Time Data'[Lead_Time]),
AVERAGE('Manual Lead Time'[Lead_Time])),30,0)
*
IF(OR(boxtypeselection=1,boxtypeselection=3),
SUM(XYZ_Sales[Revenue_Monthly_Avg]),
SUM(XYZ_Sales[Quantity_Monthly_Avg]))
,2)
,
FILTER(Coverage,Coverage[MTX Class]="MTS"),
FILTER(ABC_Sales,ABC_Sales[ABC Analysis]="A"),
FILTER(XYZ_Sales,XYZ_Sales[XYZ Analysis]="X"))
var ay =
CALCULATE(
ayservicelevel
*
SQRT(
DIVIDE(IF(OR(boxtypeselection=1,boxtypeselection=2),
AVERAGE('Lead Time Data'[Lead_Time]),
AVERAGE('Manual Lead Time'[Lead_Time])),30,0)
*
IF(OR(boxtypeselection=1,boxtypeselection=3),
POWER(sum(XYZ_Sales[Revenue_Monthly_Std]),2),
POWER(sum(XYZ_Sales[Quantity_Monthly_Std]),2))
+
IF(targettypeselection=2,
POWER(AVERAGE('Lead Time Data'[Lead_Time_Deviation])
*
IF(OR(boxtypeselection=1,boxtypeselection=3),
SUM(XYZ_Sales[Revenue_Monthly_Avg]),
SUM(XYZ_Sales[Quantity_Monthly_Avg]))
,2),0)
)
+
DIVIDE(
DIVIDE(IF(OR(boxtypeselection=1,boxtypeselection=2),
AVERAGE('Lead Time Data'[Lead_Time]),
AVERAGE('Manual Lead Time'[Lead_Time])),30,0)
*
IF(OR(boxtypeselection=1,boxtypeselection=3),
SUM(XYZ_Sales[Revenue_Monthly_Avg]),
SUM(XYZ_Sales[Quantity_Monthly_Avg]))
,2)
,
FILTER(Coverage,Coverage[MTX Class]="MTS"),
FILTER(ABC_Sales,ABC_Sales[ABC Analysis]="A"),
FILTER(XYZ_Sales,XYZ_Sales[XYZ Analysis]="Y"))
var az =
CALCULATE(
azservicelevel
*
SQRT(
DIVIDE(IF(OR(boxtypeselection=1,boxtypeselection=2),
AVERAGE('Lead Time Data'[Lead_Time]),
AVERAGE('Manual Lead Time'[Lead_Time])),30,0)
*
IF(OR(boxtypeselection=1,boxtypeselection=3),
POWER(sum(XYZ_Sales[Revenue_Monthly_Std]),2),
POWER(sum(XYZ_Sales[Quantity_Monthly_Std]),2))
+
IF(targettypeselection=2,
POWER(AVERAGE('Lead Time Data'[Lead_Time_Deviation])
*
IF(OR(boxtypeselection=1,boxtypeselection=3),
SUM(XYZ_Sales[Revenue_Monthly_Avg]),
SUM(XYZ_Sales[Quantity_Monthly_Avg]))
,2),0)
)
+
DIVIDE(
DIVIDE(IF(OR(boxtypeselection=1,boxtypeselection=2),
AVERAGE('Lead Time Data'[Lead_Time]),
AVERAGE('Manual Lead Time'[Lead_Time])),30,0)
*
IF(OR(boxtypeselection=1,boxtypeselection=3),
SUM(XYZ_Sales[Revenue_Monthly_Avg]),
SUM(XYZ_Sales[Quantity_Monthly_Avg]))
,2)
,
FILTER(Coverage,Coverage[MTX Class]="MTS"),
FILTER(ABC_Sales,ABC_Sales[ABC Analysis]="A"),
FILTER(XYZ_Sales,XYZ_Sales[XYZ Analysis]="Z"))
var bx =
CALCULATE(
bxservicelevel
*
SQRT(
DIVIDE(IF(OR(boxtypeselection=1,boxtypeselection=2),
AVERAGE('Lead Time Data'[Lead_Time]),
AVERAGE('Manual Lead Time'[Lead_Time])),30,0)
*
IF(OR(boxtypeselection=1,boxtypeselection=3),
POWER(sum(XYZ_Sales[Revenue_Monthly_Std]),2),
POWER(sum(XYZ_Sales[Quantity_Monthly_Std]),2))
+
IF(targettypeselection=2,
POWER(AVERAGE('Lead Time Data'[Lead_Time_Deviation])
*
IF(OR(boxtypeselection=1,boxtypeselection=3),
SUM(XYZ_Sales[Revenue_Monthly_Avg]),
SUM(XYZ_Sales[Quantity_Monthly_Avg]))
,2),0)
)
+
DIVIDE(
DIVIDE(IF(OR(boxtypeselection=1,boxtypeselection=2),
AVERAGE('Lead Time Data'[Lead_Time]),
AVERAGE('Manual Lead Time'[Lead_Time])),30,0)
*
IF(OR(boxtypeselection=1,boxtypeselection=3),
SUM(XYZ_Sales[Revenue_Monthly_Avg]),
SUM(XYZ_Sales[Quantity_Monthly_Avg]))
,2)
,
FILTER(Coverage,Coverage[MTX Class]="MTS"),
FILTER(ABC_Sales,ABC_Sales[ABC Analysis]="B"),
FILTER(XYZ_Sales,XYZ_Sales[XYZ Analysis]="X"))
var bby =
CALCULATE(
byservicelevel
*
SQRT(
DIVIDE(IF(OR(boxtypeselection=1,boxtypeselection=2),
AVERAGE('Lead Time Data'[Lead_Time]),
AVERAGE('Manual Lead Time'[Lead_Time])),30,0)
*
IF(OR(boxtypeselection=1,boxtypeselection=3),
POWER(sum(XYZ_Sales[Revenue_Monthly_Std]),2),
POWER(sum(XYZ_Sales[Quantity_Monthly_Std]),2))
+
IF(targettypeselection=2,
POWER(AVERAGE('Lead Time Data'[Lead_Time_Deviation])
*
IF(OR(boxtypeselection=1,boxtypeselection=3),
SUM(XYZ_Sales[Revenue_Monthly_Avg]),
SUM(XYZ_Sales[Quantity_Monthly_Avg]))
,2),0)
)
+
DIVIDE(
DIVIDE(IF(OR(boxtypeselection=1,boxtypeselection=2),
AVERAGE('Lead Time Data'[Lead_Time]),
AVERAGE('Manual Lead Time'[Lead_Time])),30,0)
*
IF(OR(boxtypeselection=1,boxtypeselection=3),
SUM(XYZ_Sales[Revenue_Monthly_Avg]),
SUM(XYZ_Sales[Quantity_Monthly_Avg]))
,2)
,
FILTER(Coverage,Coverage[MTX Class]="MTS"),
FILTER(ABC_Sales,ABC_Sales[ABC Analysis]="B"),
FILTER(XYZ_Sales,XYZ_Sales[XYZ Analysis]="Y"))
var bz =
CALCULATE(
bzservicelevel
*
SQRT(
DIVIDE(IF(OR(boxtypeselection=1,boxtypeselection=2),
AVERAGE('Lead Time Data'[Lead_Time]),
AVERAGE('Manual Lead Time'[Lead_Time])),30,0)
*
IF(OR(boxtypeselection=1,boxtypeselection=3),
POWER(sum(XYZ_Sales[Revenue_Monthly_Std]),2),
POWER(sum(XYZ_Sales[Quantity_Monthly_Std]),2))
+
IF(targettypeselection=2,
POWER(AVERAGE('Lead Time Data'[Lead_Time_Deviation])
*
IF(OR(boxtypeselection=1,boxtypeselection=3),
SUM(XYZ_Sales[Revenue_Monthly_Avg]),
SUM(XYZ_Sales[Quantity_Monthly_Avg]))
,2),0)
)
+
DIVIDE(
DIVIDE(IF(OR(boxtypeselection=1,boxtypeselection=2),
AVERAGE('Lead Time Data'[Lead_Time]),
AVERAGE('Manual Lead Time'[Lead_Time])),30,0)
*
IF(OR(boxtypeselection=1,boxtypeselection=3),
SUM(XYZ_Sales[Revenue_Monthly_Avg]),
SUM(XYZ_Sales[Quantity_Monthly_Avg]))
,2)
,
FILTER(Coverage,Coverage[MTX Class]="MTS"),
FILTER(ABC_Sales,ABC_Sales[ABC Analysis]="B"),
FILTER(XYZ_Sales,XYZ_Sales[XYZ Analysis]="Z"))
var cx =
CALCULATE(
cxservicelevel
*
SQRT(
DIVIDE(IF(OR(boxtypeselection=1,boxtypeselection=2),
AVERAGE('Lead Time Data'[Lead_Time]),
AVERAGE('Manual Lead Time'[Lead_Time])),30,0)
*
IF(OR(boxtypeselection=1,boxtypeselection=3),
POWER(sum(XYZ_Sales[Revenue_Monthly_Std]),2),
POWER(sum(XYZ_Sales[Quantity_Monthly_Std]),2))
+
IF(targettypeselection=2,
POWER(AVERAGE('Lead Time Data'[Lead_Time_Deviation])
*
IF(OR(boxtypeselection=1,boxtypeselection=3),
SUM(XYZ_Sales[Revenue_Monthly_Avg]),
SUM(XYZ_Sales[Quantity_Monthly_Avg]))
,2),0)
)
+
DIVIDE(
DIVIDE(IF(OR(boxtypeselection=1,boxtypeselection=2),
AVERAGE('Lead Time Data'[Lead_Time]),
AVERAGE('Manual Lead Time'[Lead_Time])),30,0)
*
IF(OR(boxtypeselection=1,boxtypeselection=3),
SUM(XYZ_Sales[Revenue_Monthly_Avg]),
SUM(XYZ_Sales[Quantity_Monthly_Avg]))
,2)
,
FILTER(Coverage,Coverage[MTX Class]="MTS"),
FILTER(ABC_Sales,ABC_Sales[ABC Analysis]="C"),
FILTER(XYZ_Sales,XYZ_Sales[XYZ Analysis]="X"))
var cy =
CALCULATE(
cyservicelevel
*
SQRT(
DIVIDE(IF(OR(boxtypeselection=1,boxtypeselection=2),
AVERAGE('Lead Time Data'[Lead_Time]),
AVERAGE('Manual Lead Time'[Lead_Time])),30,0)
*
IF(OR(boxtypeselection=1,boxtypeselection=3),
POWER(sum(XYZ_Sales[Revenue_Monthly_Std]),2),
POWER(sum(XYZ_Sales[Quantity_Monthly_Std]),2))
+
IF(targettypeselection=2,
POWER(AVERAGE('Lead Time Data'[Lead_Time_Deviation])
*
IF(OR(boxtypeselection=1,boxtypeselection=3),
SUM(XYZ_Sales[Revenue_Monthly_Avg]),
SUM(XYZ_Sales[Quantity_Monthly_Avg]))
,2),0)
)
+
DIVIDE(
DIVIDE(IF(OR(boxtypeselection=1,boxtypeselection=2),
AVERAGE('Lead Time Data'[Lead_Time]),
AVERAGE('Manual Lead Time'[Lead_Time])),30,0)
*
IF(OR(boxtypeselection=1,boxtypeselection=3),
SUM(XYZ_Sales[Revenue_Monthly_Avg]),
SUM(XYZ_Sales[Quantity_Monthly_Avg]))
,2)
,
FILTER(Coverage,Coverage[MTX Class]="MTS"),
FILTER(ABC_Sales,ABC_Sales[ABC Analysis]="C"),
FILTER(XYZ_Sales,XYZ_Sales[XYZ Analysis]="Y"))
var cz =
CALCULATE(
czservicelevel
*
SQRT(
DIVIDE(IF(OR(boxtypeselection=1,boxtypeselection=2),
AVERAGE('Lead Time Data'[Lead_Time]),
AVERAGE('Manual Lead Time'[Lead_Time])),30,0)
*
IF(OR(boxtypeselection=1,boxtypeselection=3),
POWER(sum(XYZ_Sales[Revenue_Monthly_Std]),2),
POWER(sum(XYZ_Sales[Quantity_Monthly_Std]),2))
+
IF(targettypeselection=2,
POWER(AVERAGE('Lead Time Data'[Lead_Time_Deviation])
*
IF(OR(boxtypeselection=1,boxtypeselection=3),
SUM(XYZ_Sales[Revenue_Monthly_Avg]),
SUM(XYZ_Sales[Quantity_Monthly_Avg]))
,2),0)
)
+
DIVIDE(
DIVIDE(IF(OR(boxtypeselection=1,boxtypeselection=2),
AVERAGE('Lead Time Data'[Lead_Time]),
AVERAGE('Manual Lead Time'[Lead_Time])),30,0)
*
IF(OR(boxtypeselection=1,boxtypeselection=3),
SUM(XYZ_Sales[Revenue_Monthly_Avg]),
SUM(XYZ_Sales[Quantity_Monthly_Avg]))
,2)
,
FILTER(Coverage,Coverage[MTX Class]="MTS"),
FILTER(Coverage,Coverage[ABC Classification]="C"),
FILTER(Coverage,Coverage[XYZ Classification]="Z"))
Var Val =
switch(max('9-Box'[Class]),
"AX",ax,
"AY",ay,
"AZ",az,
"BX",bx,
"BY",bby,
"BZ",bz,
"CX",cx,
"CY",cy,
"CZ",cz,
(ax + ay + az + bx + bby + bz + cx + cy + cz))
return if(isblank(Val),0,Val)
*
SWITCH(TRUE(),OR(VALUES('9 Box Type Selection'[Value])=1,VALUES('9 Box Type Selection'[Value])=3),
[Rate],
OR(VALUES('9 Box Type Selection'[Value])=3,VALUES('9 Box Type Selection'[Value])=4),
1,1)