Hi Experts
See the two measure below. When I use the switch measure it give me only the customer that have values based on channel selection from slicer works but formatting issues..on number
The if measure formats the values correctly negatives with brackets and % signs for % values and so on...
When i use the If measure it show all the customer even those with no data based on channel selection...and is not dynamic
I want to use the switch measure but with the formatting as shown on the IF measure..
Fist Measure Using IF
Slicer_CM_PF1 =
VAR Mth1 =
SELECTEDVALUE ( SelMth_Current[Month] )
VAR yr =
SELECTEDVALUE ( Year_Grp[Year] )
VAR Fcst1 =
SELECTEDVALUE ( SelMth_4[Actual] )
VAR Chn =
SELECTEDVALUE ( Channel_PL[Channel])
VAR Opt = 0
VAR MthVal =
CALCULATE (
SUM ( Customers_Profitability_Consolidation[Profit] ),
Customers_Profitability_Consolidation[Period] = Fcst1,
Customers_Profitability_Consolidation[Year] = yr,
Customers_Profitability_Consolidation[Channel] = Chn,
Customers_Profitability_Consolidation[Month] = Mth1
)
VAR MTDValue =
IF(
AVERAGE ( Customers_Profitability_Consolidation[Chkfmt] )=
1, FORMAT(MthVal, "#,##0.0;(#,##0.0%);-" ), IF(AVERAGE ( Customers_Profitability_Consolidation[Chkfmt] )=
2, FORMAT(MthVal, "#,##0.0;(#,##0.0);-" ),
FORMAT(MthVal, "#,##0.0;(#,##0.0);-" )
))
RETURN
MTDValue
Second Measure
Using SwitchSlicer_CM_PF1 =
VAR Mth1 =
SELECTEDVALUE ( SelMth_Current[Month] )
VAR yr =
SELECTEDVALUE ( Year_Grp[Year] )
VAR Fcst1 =
SELECTEDVALUE ( SelMth_4[Actual] )
VAR Chn =
SELECTEDVALUE ( Channel_PL[Channel])
VAR Opt = 0
VAR MthVal =
CALCULATE (
SUM ( Customers_Profitability_Consolidation[Profit] ),
Customers_Profitability_Consolidation[Period] = Fcst1,
Customers_Profitability_Consolidation[Year] = yr,
Customers_Profitability_Consolidation[Channel] = Chn,
Customers_Profitability_Consolidation[Month] = Mth1
)
VAR MTDValue =
SWITCH(
AVERAGE ( Customers_Profitability_Consolidation[Chkfmt] ),
1, MthVal *100,
2, MthVal,
MthVal
)
RETURN
MTDValue
