How to format 0.5M

[LotteLaugesen]

Hi 

I'm using this to format millions: FORMAT([TotalCost Budget]),"#,##,,.0M")

But when it is less than 1 million I get .5M and I would like to get 0.5M.

Can anybody tell me how should the format string look like?

BR

Lotte

[LotteLaugesen · Accepted solution]

I've found out 😊
FORMAT([TotalCost Budget]),"#,#0,,.0M")

[amitchandak]

@LotteLaugesen , You need try something like this. That will measure as text

SWITCH ( TRUE() ,
[M1] >= 1000000000 , CONCATENATE("$", FORMAT ([M1]/1000000000, "#,###.0") & "bn") ,
[M1] >= 1000000, CONCATENATE("$", FORMAT (COALESCE([M1], 0)/1000000, "#,###.0" )& "M") ,
[M1] >= 1000, CONCATENATE("$", FORMAT (COALESCE([M1], 0)/1000000, "#,###.0" )& "K") ,
CONCATENATE("$", FORMAT (COALESCE([M1], 0) , "#,###.0")
))

[LotteLaugesen]

Thank you, @amitchandak 

But can't I just change the format string in a way so I get the zero?  0.5M instead of .5M
FORMAT([TotalCost Budget]),"#,##,,.0M")