Number Formatting Challenge

DAX DAX Calculations
1

Hello, I think I have a tricky formatting issue, so perhaps if you do not know how to solve it, you might help by involving someone who you think might know.

I want to switch between KPI and I want to have a different formatting for sales/volume and price:

Selected = SWITCH([SelectedKPI],
    1, [Sales],
    2, [Volume],
    3, [Price])

Using in the formula Format( [Price], “0.00”) renders e.g. 1.71 - which is great for the US, but in Europe I need to have 1,71. Using “Fixed” gives the same result.

In the regional setting the locale is set to German (Germany). But that is only for the import.
My Windows 10 setting seems to be set correctly

image
image525×752 17.2 KB

Any idea how sales and volume could be displayed with different digits then the price:
image
image

Hope the explanation was clear enough and if you miss any information, please let me know. Thanks!

2

Hi @Matthias , we aim to consistently improve the topics being posted on the forum to help you in getting a strong solution faster. While waiting for a response, here are some tips so you can get the most out of the forum and other Enterprise DNA resources.

  • Use the forum search to discover if your query has been asked before by another member.
  • When posting a topic with formula make sure that it is correctly formatted to preformatted text </>.

image

  • Use the proper category that best describes your topic
  • Provide as much context to a question as possible.
  • Include the masked demo pbix file, images of the entire scenario you are dealing with, screenshot of the data model, details of how you want to visualize a result, and any other supporting links and details.

I also suggest that you check the forum guideline How To Use The Enterprise DNA Support Forum. Not adhering to it may sometimes cause delay in getting an answer.

Please also check the How To Mask Sensitive Data thread for some tips on how to mask your pbix file.

3

Hi there,

I hope the following link can be useful in solving this issue:

Good luck…

4

@IlgarZarbaliyev Thanks a lot for your reply Ilgar! It seems that I am out of luck. :worried: The data from the query are whole numbers (volume) and decimal numbers (sales). Sales is k€ so it makes sense not to go to fixed decimal number.
My issue is that I need to format the resulting measure. And if I use for this the FORMAT function I only get the US result.
The only formatting solution I know is using the Format field in the Measure tools tab (s. example below). And that solution I can not apply because of the SWITCH function for sales, volumn and price.
image

5

Hi @Matthias - What I understood from your requirement is you want to dynamically change Locale (Region Settings) of your measure.

Unfortunately this doesn’t seems to be possible in Power BI at the moment. I have checked Online as well and there is no such option available.

Thanks
Ankit J

6

Hi @ankit, thanks for your endeavors!
I do not want to change the Locale (Region Settings) - Germany is fine with me for all 3 KPI.
What I want are two digits for [Price].

I can get two digits by using FORMAT( [Price], “0.00”) or FORMAT( [Price], “Fixed”). But I get that with a dot (e.g. 1.71) instead the for me regular comma (1,71).

I hope that this clearifies the issue. And I fear that it doesn’t change the answer. It is surprising that the FORMAT formula is basically useless for many none US users. [In VBA you have to use the US formatting syntax too, but the results are rendered in the correct regional setting.]

Thanks
Matthias

7

@Matthias

disclaimer: I do not have access to a computer with a different regional setting, and am in the US, so I can’t test this for you

However, in reading the MS remarks on DAX format function, it seems that you should be able to get the proper format for your region easily.

image
image981×113 6.82 KB

so perhaps you need to take a look at the regional settings are for the PowerBI file you are working with

one warning, using FORMAT to change the display of a value will change it from a number into a text string (annoying, but workable once you know this)

1 Like
8

Thanks @Heather!
You got it, and the reason I can’t change it is “For new Power BI Desktop models”.
So for existing reports I won’t be able to use FORMAT to get the desired formatting. But with all new reports, it should work for me (but then probably my US colleagues will see comma instead of dot).

That answers also why in contrast to FORMAT the US syntax used in the ribbon (s. picture) worked fine:
image

Thanks again,
Matthias

9

happy to help, but it’s unfortunate that this doesn’t really solve the issue for you