cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Jonathan_P
Helper II
Helper II

Always use the default regional settings

Hello,

 

First of all, this is my first post ever here. So, hello community!

 

I found that Power BI Desktop always use the default regional settings for the selected format on the report display. If those settings are customized, they are ignored, at least for the numbers and currency. In my case, I selected "Français (Canada)" as the format but changed the decimal and the digit grouping symbols. See the screenshoot.

 

 

Thank you

 

Power BI.jpg

1 ACCEPTED SOLUTION

Hi @Jonathan_P,

As you want to keep "Français (Canada)" as the format on system regional setting and want to show currency as "English (United States)" in Power BI Desktop. You can use Format function to change its format, I test it successful using your PBIX file.

Just maks some changes  to your measures, for example:

YTD Sales = FORMAT(TOTALYTD([Total Sales]; 'Date'[Date]);"Currency")
LY Sales = FORMAT( CALCULATE([Total Sales]; SAMEPERIODLASTYEAR('Date'[Date])); "Currency")
Sales Var = FORMAT(( [Total Sales] - [LY Sales]);"Currency")

Thanks,
Lydia Zhang

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

13 REPLIES 13
v-yuezhe-msft
Microsoft
Microsoft

Hi @Jonathan_P,

That how Power BI Desktop reports display numbers and currency has nothing to do with the system Regional setting as shown in your screenshot. In Power BI Desktop, if you want to format your numbers, please perform the following steps.

1. Click your column in the Fields panel of the following screenshot, then click on Modeling in the Desktop ribbon, select Thousands operator and set Decimal places to 2.
1.PNG

2. Change the format to Currency>$ French(Canada), the positive and negative values will display in your desired format.
2.PNG


Thanks,
Lydia Zhang

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Hi @v-yuezhe-msft,

 

First, I want to clarify. Again, I have selected "Français (Canada)" as the format in my system regional settings and this is fine for me. I just wanted to show temporary the values differently on the reports and as the "English (United States)" format's default. So I customized "Français (Canada)" and changed the decimal and the digit grouping symbols respectively to "." and ",". I restarted Power BI Desktop and saw that it had no impact on the reports. Then I found that selecting "English (United States)" as the format worked. I am concluding that Power BI Desktop use the format's default settings only. So the reports display has something to do with the regional settings set on Windows. However, maybe that this behaviour is by-design.

 

On the step 1 you provided, that only indicates how the values of a column are displayed:

1) If it displays the thousands separator or not. So, the format's default "Digit grouping symbol" from the regional settings is used here.

2) How many decimal places are displayed. Interestingly, here the default is taken from the customized regional settings value (as opposed to the decimal and the digit grouping symbols).

 

On the step 2, I agree that it's useful. But in my particular report I chose "$ English (United States)" and it's fine.

 

Thank you

Hi @Jonathan_P,

I re-test the scenario as yours. When I firstly create a table visual using sample data in figure 1.
1.PNG
                          figure 1

The table visual looks like as shown in figure 2.
3.PNG

                                                    figure 2

When I selected "Français (Canada)" as the format , changed the decimal and the digit grouping symbols as yours, the visual displayed is shown in figure 3.
4.PNG
                                                           figure 3

When I changed it back to English (United States),  I also tried to changed the decimal in this case, the table visual is still same as figure 2.  Thus, I recommend you directly format your columns using modeling tab in Power BI Desktop rather than using system regional settings becuase the customized formats for number and currency in English (United States) or Français (Canada) are both ignored.

Thanks,
Lydia Zhang

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

It's ignored. Is it by-design?

 

No, I can't use the modeling tab to format columns completely. There is no way to change decimal and digit grouping symbols. Maybe I'm missing something...

 


No, I can't use the modeling tab to format columns completely. There is no way to change decimal and digit grouping symbols. Maybe I'm missing something...

 


Hi @Jonathan_P,

Thanks for sharing me the PBIX file. Could you please describe more details about how you want to format columns in modeling tab and change decimal, digit grouping symbols in Power BI Desktop?

I make a test in your PBIX file, I can select Thousands operator and set Decimal places to different values for columns such as YTD sales. You can check the following screenshot.

1.PNG



Thanks,
Lydia Zhang

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Hi @v-yuezhe-msft

 

Please refer again to my first 2 posts. But in short, the "Additional settings..." in regional settings are ignored in most cases. If I want to keep "Français (Canada)" as the format but want to show, as an example, currency as "English (United States)" using the dot (".") for "Decimal symbol", I can't.

 

Thank you

Hi @Jonathan_P,

As you want to keep "Français (Canada)" as the format on system regional setting and want to show currency as "English (United States)" in Power BI Desktop. You can use Format function to change its format, I test it successful using your PBIX file.

Just maks some changes  to your measures, for example:

YTD Sales = FORMAT(TOTALYTD([Total Sales]; 'Date'[Date]);"Currency")
LY Sales = FORMAT( CALCULATE([Total Sales]; SAMEPERIODLASTYEAR('Date'[Date])); "Currency")
Sales Var = FORMAT(( [Total Sales] - [LY Sales]);"Currency")

Thanks,
Lydia Zhang

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Ok thanks @v-yuezhe-msft!

Hi @Jonathan_P,

I haven't found any official information about if it is by-design. I will report this issue internally and post back once I get any updates.

In addition, could you please describe more details about that how you format the columns in Power BI Desktop? Do you click the column in Fields panel before you go to modeling tab?

Thanks,
Lydia Zhang

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Yes, I click on the column in the Fields panel before going to the modeling tab.

Hi @Jonathan_P,


What is your data source? How do you connect to the data source in Power BI Desktop? Please help to post the screenshots of your scenario.

Thanks,
Lydia Zhang

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

I don't know which screenshots to provide. The data source is an Access database. This is one of the data source used for the course on edX.org web site here. The lab in particular is Lab 2. This is not a real-life case. I just found that this was maybe a bug or wanted to ask if it was by-design. I wanted to understand it. The question was raised when I exported the data in csv.

Hi @Jonathan_P


Do you import CSV file or Access database to Power BI Desktop and then format the column?  Would you mind sharing me the PBIX file or data source? I will test it in my Desktop.

Thanks,
Lydia Zhang

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Helpful resources

Announcements
May 23 2022 epsiode 5 without aka link.jpg

The Power BI Community Show

Welcome to the Power BI Community Show! Jeroen ter Heerdt talks about the importance of Data Modeling.

charticulator_carousel_with_text (1).png

Charticulator Design Challenge

Put your data visualization and design skills to the test! This exciting challenge is happening now through June10th!

Power BI Dev Camp Session 22 without aka link and time 768x460.jpg

Check it Out!

Watch Session 22 Ted's Dev Camp along with past sessions!

Power BI Release May 2022 768x460.png

Check it out!

Click here to read more about the May 2022 updates!

Top Kudoed Authors