Join us for an expert-led overview of the tools and concepts you'll need to pass exam PL-300. The first session starts on June 11th. See you there!
Get registeredPower BI is turning 10! Let’s celebrate together with dataviz contests, interactive sessions, and giveaways. Register now.
can anyone help me to format million sperator to Crore, lakh seperator like 123,456,789.00 to 12,34,56,789.00 ?
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
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
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
User | Count |
---|---|
84 | |
82 | |
66 | |
52 | |
46 |
User | Count |
---|---|
100 | |
48 | |
42 | |
39 | |
38 |