Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Power BI is turning 10! Let’s celebrate together with dataviz contests, interactive sessions, and giveaways. Register now.

Reply
pavitrajindal
New Member

Localization numeric formatting (periods and commas)

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

3 REPLIES 3
Anonymous
Not applicable

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"

 

Capture.PNG

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

 

Regards,

Xiaoxin Sheng

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

Anonymous
Not applicable

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

Helpful resources

Announcements
June 2025 Power BI Update Carousel

Power BI Monthly Update - June 2025

Check out the June 2025 Power BI update to learn about new features.

May 2025 Monthly Update

Fabric Community Update - May 2025

Find out what's new and trending in the Fabric community.