cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
RacAgr Regular Visitor
Regular Visitor

Thousand-Separator-and-Decimal-Separator

hello,

 

for the currency column, I'm having problems with decimal separator and thousand separator.

 

Decimal separator is a comma and thousand separator is a point. But PowerBi goes wrong

 

for example - powerbi shows 121,000.00 (thousand separator is a comma and Decimal separator is a point)

 

Output expected, 121.000,00 (Decimal separator is a comma and thousand separator is a point)

 

Thanks

1 ACCEPTED SOLUTION

Accepted Solutions
MarcelBeug Super Contributor
Super Contributor

Re: Thousand-Separator-and-Decimal-Separator

The display of numeric felds (and dates, times etecetera) depends on your Regional Settings.

 

It can be adjusted in Power BI Desktop or in Windows (Land/Region). It looks like the Windows setting is leading, but you have to close and reopen your pbix file after adjusting the Regional Settings.

 

Regional Settings.png

 

In Power BI you can also adjust your language settings to influence the display format for published reports.

 

 

Specializing in Power Query Formula Language (M)

View solution in original post

12 REPLIES 12
vanessafvg Super Contributor
Super Contributor

Re: Thousand-Separator-and-Decimal-Separator

@RacAgr

 

this is the correct format as far as i know.

 

powerbi shows 121,000.00

 

 


Did I answer your question? Mark my post as a solution.
Proud a to be a Datanaut!
RacAgr Regular Visitor
Regular Visitor

Re: Thousand-Separator-and-Decimal-Separator

any suggestions pls on how to achieve the expected output?

Community Support Team
Community Support Team

Re: Thousand-Separator-and-Decimal-Separator

Hi @RacAgr,

 

Based on test, if I export these data to csv, it will remove custom format and keep the normal number. So I think you store these value with text format, right?

 

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin Sheng
If this post helps, please consider Accept it as the solution to help the other members find it more quickly
Learning resources: Power BI
RacAgr Regular Visitor
Regular Visitor

Re: Thousand-Separator-and-Decimal-Separator

these values are in decimal number then using currency 

Community Support Team
Community Support Team

Re: Thousand-Separator-and-Decimal-Separator

Hi @RacAgr,

 

I can't reproduce your issue, It works well on my side.

10.PNG

 

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin Sheng
If this post helps, please consider Accept it as the solution to help the other members find it more quickly
Learning resources: Power BI
RacAgr Regular Visitor
Regular Visitor

Re: Thousand-Separator-and-Decimal-Separator

Output expected, 121.000,00 (Decimal separator is a comma and thousand separator is a point)

 

 

in your result, you get 121,000.00 (Decimal separator is a point and thousand separator is a comma), which i am able to get as well. but i need the output as shown in first line

MarcelBeug Super Contributor
Super Contributor

Re: Thousand-Separator-and-Decimal-Separator

The display of numeric felds (and dates, times etecetera) depends on your Regional Settings.

 

It can be adjusted in Power BI Desktop or in Windows (Land/Region). It looks like the Windows setting is leading, but you have to close and reopen your pbix file after adjusting the Regional Settings.

 

Regional Settings.png

 

In Power BI you can also adjust your language settings to influence the display format for published reports.

 

 

Specializing in Power Query Formula Language (M)

View solution in original post

ShuwiDBA Frequent Visitor
Frequent Visitor

Re: Thousand-Separator-and-Decimal-Separator

Has something recently changed in this behaviour? I can't seem to get the xxx.xxx,xx formatting using the Locale setting.. 

ShuwiDBA Frequent Visitor
Frequent Visitor

Re: Thousand-Separator-and-Decimal-Separator

I found the solution to my issue; PBI Desktop seems to be only looking at the "Region - Format" part in the Win10 - Control Panel - Clock and Region - Region settings e.g:

Format: Dutch (Netherlands). When customizing this format using the "Additional settings" button, these customizations are not taken into account by PowerBI Desktop; So, it's VERY important to pick the correct Format: selection and not customize an existing selection to the needs of your users. tmp_pbi.JPG

 Created a blogpost around my findings on this. 

 

Helpful resources

Announcements
New Topics Started Badges Coming

New Topics Started Badges Coming

We're releasing new versions of the badge that everyone's talking about. ;) Check your inbox for notifications.

MBAS 2020

Save the new date (and location)!

Our business applications community is growing—so we needed a different venue, resulting in a new date and location. See you there!

Difinity Conference

Difinity Conference

The largest Power BI, Power Platform, and Data conference in New Zealand

Top Solution Authors
Top Kudoed Authors (Last 30 Days)