Thousand Separtors

[ArunkumarK]

Hello Everyone,

I needs to convert the number into US thousand separtors like below. How to conver the number into highlighed formats like below.

Available Number: 125000

Available Format: 1,25,000

Required Format: 125,000

Thanks in Advance

[Anonymous · Accepted solution]

Hi Arun,

For me it defaults to the US version when I select the field and select comma in modeling. If your default is diferent, you could perhaps force it using Format command such as 

newdata = FORMAT(data[Column1],"#,0.")

[ArunkumarK]

Nice. Format formula is working fine.

Thanks you so much.

[Anonymous]

You are welcome.  Glad it worked for you.

[ArunkumarK]

Hi,

The idea you suggested is working but it creates another problem. When using the format function the number is converted into text. When i tried value(Format([col]),"#,0") then the thousand separator disapper. Something found  but not the output as expected.

Buzz me if you have any idea.

Thanks in advance.

[Anonymous]

Hi Arun,

when I used the value function to cast the text as a number, it did keep the commas but added decimal places.  

newdata = value(FORMAT(data[Column1],"#,0."))  

I also changed the original column to a text field and the 'newdata' still came out formatted correctly ( but with decimals).

[ArunkumarK]

I assume my words make you confused. Even I get the answer same what you got but when you sum the column values it wont come because of it converted into text.

E.g If a column have multiple rows with data and we formated in such a way then in the report section if you try to sum, it wont allow except count.

Hope make sense.

[Anonymous]

Yes, you are right, I am not quite following.  I am getting commas correctly when displaying the numbers individually or as a sum (see image).  I added a few more rows and then showing a sum in a cardfile.  Sorry, I am not sure how to help you further since I am getting the correct data (as I understand it). 

[ArunkumarK]

Thanks for your understanding and response.

But still I am sure the table shown below is just displaying the data but if you try to display the total it wont come if you formatted using Format Funct.

[Anonymous]

I understand the 2nd issue now. Using the value function causes the Sigma sign next to the column to disappear, even though it is technically a number.  As I understand it, only columns with that sign will show totals within a table or matrix.  You can still show sums at a row level within the table or in cards, but it will not show total within the table. You may want to pose that as another question in the community and see if anyone else has ideas on showing totals in a table when using Value.   

[Anonymous]

Hi Arun,

ok, I got this to work just by adding a new column that was a mirror of the newdata column I had created.  Essentially I created a new calculated column: 

newdata2 = data[newdata]

Here is the image of it working with a table total: