topic Re: How to format 0.5M in DAX Commands and Tips

How to format 0.5M

LotteLaugesen — Tue, 26 Jul 2022 07:41:40 GMT

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

Re: How to format 0.5M

amitchandak — Tue, 26 Jul 2022 07:58:42 GMT

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")
))

Re: How to format 0.5M

LotteLaugesen — Tue, 26 Jul 2022 09:58:07 GMT

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")

Re: How to format 0.5M

LotteLaugesen — Tue, 26 Jul 2022 10:04:50 GMT

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