Switch dax measure with numbers format issue

[Anonymous]

Hi Experts

The following measure below works perfectly fine when I remove the formatting element from the dax. When I apply the formatting which I would like. It bring back all my customers including those with no data..

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, FORMAT(MthVal, "#,##0.0;(#,##0.0%);-" ),

2, FORMAT(MthVal, "#,##0.0;(#,##0.0);-" ),

FORMAT(MthVal, "#,##0.0;(#,##0.0);-" )

RETURN

MTDValue

[amitchandak]

@Anonymous , better to use calculation groups for this

https://www.sqlbi.com/articles/introducing-calculation-groups/

Using format will convert it into string

[Anonymous]

Not sure i follow - still stuck?