I am trying to shorten the following measure to something that is more manageable. Totally stuck
__v_Slicer_CFO_FY =
VAR Grpmth =
SELECTEDVALUE ( SelMth[Actual] )
VAR Regn =
SELECTEDVALUE ( Channel_Grp[Channel] )
VAR Opt = 0
VAR mth1 =
SWITCH (
AVERAGE ( Consolidated_Financial_Reports[Chkfmt] ),
1,
(
CALCULATE (
SUM ( Consolidated_Financial_Reports[FY_CM_Fcst] ),
Consolidated_Financial_Reports[Channel] = Regn,
Consolidated_Financial_Reports[C_Month] = Grpmth
) / 1000
),
2,
(
FORMAT (
(
CALCULATE (
SUM ( Consolidated_Financial_Reports[FY_CM_Fcst] ),
Consolidated_Financial_Reports[Channel] = Regn,
Consolidated_Financial_Reports[C_Month] = Grpmth
)
),
"##0.0%;(##0.0%);-"
)
),
3,
(
CALCULATE (
SUM ( Consolidated_Financial_Reports[FY_CM_Fcst] ),
Consolidated_Financial_Reports[Channel] = Regn,
Consolidated_Financial_Reports[C_Month] = Grpmth
)
)
)
VAR mth2 =
SWITCH (
AVERAGE ( Consolidated_Financial_Reports[Chkfmt] ),
1,
(
CALCULATE (
SUM ( Consolidated_Financial_Reports[FY_PM_Fcst] ),
Consolidated_Financial_Reports[Channel] = Regn,
Consolidated_Financial_Reports[C_Month] = Grpmth
) / 1000
),
2,
(
FORMAT (
(
CALCULATE (
SUM ( Consolidated_Financial_Reports[FY_PM_Fcst] ),
Consolidated_Financial_Reports[Channel] = Regn,
Consolidated_Financial_Reports[C_Month] = Grpmth
)
),
"##0.0%;(##0.0%);-"
)
),
3,
(
CALCULATE (
SUM ( Consolidated_Financial_Reports[FY_PM_Fcst] ),
Consolidated_Financial_Reports[Channel] = Regn,
Consolidated_Financial_Reports[C_Month] = Grpmth
)
)
)
VAR mth3 =
SWITCH (
AVERAGE ( Consolidated_Financial_Reports[Chkfmt] ),
1,
(
CALCULATE (
SUM ( Consolidated_Financial_Reports[FY_BBP] ),
Consolidated_Financial_Reports[Channel] = Regn,
Consolidated_Financial_Reports[C_Month] = Grpmth
) / 1000
),
2,
(
FORMAT (
(
CALCULATE (
SUM ( Consolidated_Financial_Reports[FY_BBP] ),
Consolidated_Financial_Reports[Channel] = Regn,
Consolidated_Financial_Reports[C_Month] = Grpmth
)
),
"##0.0%;(##0.0%);-"
)
),
3,
(
CALCULATE (
SUM ( Consolidated_Financial_Reports[FY_BBP] ),
Consolidated_Financial_Reports[Channel] = Regn,
Consolidated_Financial_Reports[C_Month] = Grpmth
)
)
)
VAR mth4 =
SWITCH (
AVERAGE ( Consolidated_Financial_Reports[Chkfmt] ),
1,
(
CALCULATE (
SUM ( Consolidated_Financial_Reports[FY_PY_Act] ),
Consolidated_Financial_Reports[Channel] = Regn,
Consolidated_Financial_Reports[C_Month] = Grpmth
) / 1000
),
2,
(
FORMAT (
(
CALCULATE (
SUM ( Consolidated_Financial_Reports[FY_PY_Act] ),
Consolidated_Financial_Reports[Channel] = Regn,
Consolidated_Financial_Reports[C_Month] = Grpmth
)
),
"##0.0%;(##0.0%);-"
)
),
3,
(
CALCULATE (
SUM ( Consolidated_Financial_Reports[FY_PY_Act] ),
Consolidated_Financial_Reports[Channel] = Regn,
Consolidated_Financial_Reports[C_Month] = Grpmth
)
)
)
VAR optn_Mth =
IF (
CONTAINS ( Measure_Slicer_2, Measure_Slicer_2[Measure], "Current Fcst" ),
1,
0
)
VAR optn_YTD =
IF (
CONTAINS ( Measure_Slicer_2, Measure_Slicer_2[Measure], "Prior Fcst" ),
2,
0
)
VAR optn_FY =
IF (
CONTAINS ( Measure_Slicer_2, Measure_Slicer_2[Measure], "Budget(BBP)" ),
3,
0
)
VAR optn_Q1 =
IF (
CONTAINS ( Measure_Slicer_2, Measure_Slicer_2[Measure], "Prior Year Actual" ),
4,
0
)
VAR Tot_Opt = optn_Mth + optn_YTD + optn_FY + optn_Q1
RETURN
SWITCH ( Tot_Opt, 1, mth1, 2, mth2, 3, mth3, 4, mth4 )
