Is number format in Canada incorrect or am I doing something wrong?

[famigami]

In English Canada, according to the Government of Canada's guidelines at numbers: decimal fractions all thousands separators should be spaces both to the left and to the right of the decimal fraction indicator except with currency, where a comma should be used instead of a space.

However, in Power BI (and all Office software and Windows itself too) the comma is used everywhere at all times. Why is this and how do I set it to use the correct format?

[danextian · Accepted solution]

You can try these format strings which are in the link in my initial post

### ### ##0.00# ### ### \$;(### ### ##0.00# ### ### \$);### ### ##0.00# ### ### \$

### ### ##0.00# ### ### \$: Positive number format.
(### ### ##0.00# ### ### \$): Negative number format (wrapped in parentheses).
### ### ##0.00# ### ### \$: Zero value format.

Removing  " \$" will remove the dollar sign

[Anonymous · Accepted solution]

Hi @famigami ,

Please follow the steps below:

1. This is my original data:

2. You could change the format setting if you need use a space to separate groups of three digits.

3. Sums of money:

Best regards,

Lucy Chen

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[shafiz_p]

Hi @famigami 

The default use of commas as thousands separators and periods as decimal points in Power BI is primarily due to the standardization of DAX syntax. Starting from May 2020, Power BI adopted a consistent approach to separators, regardless of the Windows locale settings. This change was made to simplify the user experience and ensure consistency across different environments.

Here are a few reasons for this decision:

1. Consistency
2. Compatibility
3. Simplification

If you prefer to use the Canadian format with spaces as thousands separators, you can customize the settings in Power BI Model View. 

Go to model view > select column which you want a candian format > Go to properties pane > set data type > select custom format > set format to ### ### ###.##

See image:

Hope this helps!!

If this solved your problem, please accept it as a solution and a kudos!!

Best Regards,
Shahariar Hafiz

[famigami]

but what about space to the RIGHT of the decimal such as 123 456.789 101 

[famigami]

but what about spae to the RIGHT of the decimal such as 123 456.789 101 

[danextian · Accepted solution]

You can try these format strings which are in the link in my initial post

### ### ##0.00# ### ### \$;(### ### ##0.00# ### ### \$);### ### ##0.00# ### ### \$

### ### ##0.00# ### ### \$: Positive number format.
(### ### ##0.00# ### ### \$): Negative number format (wrapped in parentheses).
### ### ##0.00# ### ### \$: Zero value format.

Removing  " \$" will remove the dollar sign

[danextian]

Hi @famigami 

Power BI doesn't currently have an option to use space as thousand sepators however each number columns and measures can be formatted individually. A similar post  had already been made before. Please refer to this:

https://community.fabric.microsoft.com/t5/Desktop/How-to-format-data-numbers-to-Canadian-French-curr...