FORMAT() of numbers not using regional settings

[Anonymous]

Hello

I have a problem with Format function when it comes to regional settings. My regional setting is Dutch (Netherlands) for current file, so thousand separator is dot, which is okay when I display it as a numer:

The problem is when I display it as string with help of a Format function which stands for: 

text2 = format([total];"0,0")

Which is wrong because I need to display the number with dot as thousand separator not comma.

Can someone help? Maybe I use Format type "0,0" in a wrong way?

[Anonymous · Accepted solution]

Hi @Anonymous,

It seems like the default output type does not suitable for your requirement, you can try to use custom format strings with 'common' format and add locale option to change the return results. (notice: the comma and dot characters have special definitions in the custom string so you can't directly replace the thousand and decimal separators to achieve your requirements)

Measure = 
FORMAT ( SUM ( 'Table'[Amount] ), "#,##0", "nl_NL" )

Regards,

Xiaoxin Sheng

[amitchandak]

@Anonymous , In the column and measure tools you have a thousand separators, Try that.

Also use you regional separator and check -as setting is regional

[Anonymous]

Hi

but unfortunately it is a text so i dont have that option with separators:

[Anonymous]

HI @Anonymous,

In my opinion, using the format function should not be a good choice. This function will force convert the result to text type which will ignore the aggregation features.

I'd like to suggest you check and modify the system region 'formats' settings or use custom format string on your field to change the display value formats.

Solved: Re: Decimal separator : Comma / Thousand separato... - Microsoft Power BI Community

Regards,

Xiaoxin Sheng

[Anonymous]

HI @Anonymous 

unfortunately I have to do the formatting this way because I need to implement the value as text in shape. But I had the idea that I could use the SUBSTITUTE function for the text and thus replace the comma for the dot.

[Anonymous]

HI @Anonymous,

Perhaps you can take a look at the following link about format function usage if helps:

FORMAT function (DAX) - DAX | Microsoft Docs

Sample: format with locale option.

Measure =
FORMAT ( SUM ( 'Table'[Amount] ), "Standard", "nl_NL" )

List of Country Language Locale Code - saimana.com

Regards,

Xiaoxin Sheng

[Anonymous]

Hi @Anonymous 

Thank you very much. the measure written above works, but there is one problem that it displays a decimal value:

and I need a view:

So how can I change the "Standard" field?

[Anonymous · Accepted solution]

Hi @Anonymous,

It seems like the default output type does not suitable for your requirement, you can try to use custom format strings with 'common' format and add locale option to change the return results. (notice: the comma and dot characters have special definitions in the custom string so you can't directly replace the thousand and decimal separators to achieve your requirements)

Measure = 
FORMAT ( SUM ( 'Table'[Amount] ), "#,##0", "nl_NL" )

Regards,

Xiaoxin Sheng

[Anonymous]

Hi @Anonymous 

It works in Power BI Desktop, but when I publish it into Power BI Server I can't see visuals:

And there is an error:

What should I do?

[Anonymous · Accepted solution]

HI @Anonymous,

It seems like the power bi service does not support some detailed locale. After I change locale code to 'nl' (Dutch locale code) the visual works again on power bi service. 

Measure = 
FORMAT ( SUM ( 'Table'[Amount] ), "#,##0", "nl" )

Regards,

Xiaoxin Sheng