Localization numeric formatting (periods and commas)

[pavitrajindal]

can anyone help me to format million sperator to Crore, lakh seperator like 123,456,789.00 to 12,34,56,789.00 ?

[v-shex-msft]

Hi @pavitrajindal,

Take a look at below formula if it suitable for your requirment:

Format =
var b= INT(MOD([Amount],10000000)/100000)
var c= INT(MOD([Amount],100000)/1000)
var d= INT(MOD([Amount],1000))
return
IF(LEN([Amount])>=6,b&","&REPT("0",2-LEN(c))&c&","&REPT("0",3-LEN(d))&d,if(LEN([Amount])>=4,c&","&REPT("0",3-LEN(d))&d,d))&".00"

Notice, Above formula will convert the type to text and it  is hard to convert the formatted text to numeric type.

Regards,

Xiaoxin Sheng

[pavitrajindal]

i need number to be in Numeric format not in string format, is there anything like we do in Excel using custome format ?

Also my number is big it is in billions

[v-shex-msft]

Hi @pavitrajindal,

I haven't found a way to format numeric value and keep it's type.
In addition, you can also use format function to format the display text(it also change the columns type)

Format =
IF (
    [Amount] >= 1000000,
    FORMAT ( [Amount], "#,#0,##0" ),
    IF (
        [Amount] >= 1000,
        FORMAT ( [Amount], "#,##0" ),
        FORMAT ( [Amount], "General Number" )
    )
)

Regards,

Xiaoxin Sheng