Forum Discussion
Switch and If Measure - Tricky Question
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 Switch
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 =
SWITCH(
AVERAGE ( Customers_Profitability_Consolidation[Chkfmt] ),
1, MthVal *100,
2, MthVal,
MthVal
)
RETURN
MTDValue
- 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);-" )))1. you cannot mix values and percentages in the same format command.2. the second option is not neededChange your code toVAR MTDValue =IF(AVERAGE ( Customers_Profitability_Consolidation[Chkfmt] )=1, FORMAT(MthVal, "#,##0.0%;(#,##0.0%);-" ),FORMAT(MthVal, "#,##0.0;(#,##0.0);-" ))
2 Replies
- 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);-" )))1. you cannot mix values and percentages in the same format command.2. the second option is not neededChange your code toVAR MTDValue =IF(AVERAGE ( Customers_Profitability_Consolidation[Chkfmt] )=1, FORMAT(MthVal, "#,##0.0%;(#,##0.0%);-" ),FORMAT(MthVal, "#,##0.0;(#,##0.0);-" ))