Dynamics F/SCM Customer Experience (CRM) Dynamics GP Dynamics Business Central (NAV) Dynamics SL Power Platform Fabric Copilot

[Vishesh Jain]

Hello everyone,

I have been looking for this and have not be able to find the right solution yet.

The default format string for numbers in PBI is million, billion etc.

I want to format the numbers in Indian format like lacks, crores etc., which look like this

Lac = 1,00,000
10 Lac(million) = 10,00,000
Crore (10 Million) = 1,00,00,000

The thousand separator uses million format and I have changed the language and locale to India but still has not solved the problem.
I do not want to divide the number and display it as 1 Lac or 1 Crore, I want the thousand separator to be at every 2 digits after the hundred.

If anyone has any information or solution to this then please let me know.

Thank you,

——————————
Vishesh Jain
Owner
VR Construction
——————————

[Lutz Bendlin]

CroreLakhs = format(sum([yourvalue]),”#,##,##,###.##”)

You can’t use the thousands separator, you need to explicitly use the comma, and for that you need to escape it with a backslash.

——————————
Lutz
——————————
——————————————-

[Vishesh Jain]

Hello ,

Thank you for responding.

I am trying to put the format string in the display format of the column/measure and hardcode it in a measure., i.e. from the relationship view, where you can provide a custom string.
Also if I use the FORMAT() function, it will convert the number into a text and I do not want that.

I tried using the code you sent, and I am getting an extra comma, in front of values that are less than 10 million.

After the last 3 digits ROUNDED OFF to 0, I would like the thousand separator after every 2 digits, i.e. 00,00,00,00,00,000

Please let me know if you or anyone else has a solution.

Thank you,

——————————
Vishesh Jain
Owner
VR Construction
——————————
——————————————-

[Lutz Bendlin]

You can prepare a format string that covers your expected number range (let’s say up to one nil)  and then you can use a number size check (using the LOG10 function) to only use the (right ended) part of the string that is required.

“Also if I use the FORMAT() function, it will convert the number into a text and I do not want that.”

I do not understand this statement. Are you trying to do math after the formatting? The formatting should be the final step just before you render the visual.

“I would like the thousand separator after every 2 digits”

That is impossible. It’s called a thousands separator for a reason. It will always kick in after every three digits.

——————————
Lutz
——————————
——————————————-

[Vishesh Jain]

Hello ,

I am trying to change the format of the number from the relationship view. I hope the picture will clear out any confusion.

I do NOT want to change the format of the number to text, I just want to change the display format of the number.

I want the commas between numbers to be like this 00,00,00,00,00,000 and I do not want a comma preceding the number if the number is small.

I hope this clears out any confusion.

Thank you,

——————————
Vishesh Jain
Owner
VR Construction
——————————
——————————————-

[Lutz Bendlin]

I see two options – either you can agree with me that formatting a number as text is _exactly_ how any parts of visuals work that display numbers,  or you can raise an idea to support Indian number formats natively in Power BI (as they were back in  2018)

——————————
Lutz
——————————
——————————————-

[Vishesh Jain]

As long as I can do calculations on it, the data type of the column/measure does not change and it displays the way I would like it to, I’m all ears if you have any suggestions.

Thank you,

——————————
Vishesh Jain
Owner
VR Construction
——————————
——————————————-

[Lutz Bendlin]

I already gave my final suggestion earlier.

“You can prepare a format string that covers your expected number range (let’s say up to one nil)  and then you can use a number size check (using the LOG10 function) to only use the (right ended) part of the string that is required.”

——————————
Lutz
——————————
——————————————-

[Vishesh Jain]

Thank you for your suggestion.

I do not know how to write that format string, so it possible for you or anyone else to please write the code and then I can try to edit it.

Thank you,

——————————
Vishesh Jain
Owner
VR Construction
——————————
——————————————-

[Lutz Bendlin]

Measure = 
var yournumber = <your number here>
var digits = ROUNDUP((LOG10(yournumber)-3)/2,0)
var f = rept("##,",digits) & "###.00"
return format(yournumber,f)

 

——————————
Lutz
——————————
——————————————-

[Gopa Kumar Sivadasan]

Hi

Is your issue resolved? If not, please see if the following helps you.

Is your issue from the Power BI service or Power BI Desktop? Is it that despite selecting the currency display format of the measure as ‘English-India’ in the pbix, the display in Power BI service reverts to millions while in desktop it displays properly?

If the issue is with the Power BI service, despite having the proper currency display format in desktop, the issue could be the language setting of your browser, through which you are accessing Power BI Service. To change the language setting of the browser, please see the below images:

1. Edge Browser

2. Chrome Browser

——————————
Gopa Kumar
Limner Consulting

North Kerala User Group Leader
https://www.pbiusergroup.com/NorthKerala
——————————
——————————————-

[Vishesh Jain]

Hi ,

??I have made the changes as you asked for it and it has worked in the PBI Desktop.

However, even after making the changes in the desktop and the browser the numbers are still showing up at millions on the service and on the PBI app as well.

Please can you suggest what am I missing here.

Thank you,

——————————
Vishesh Jain
Owner
VR Construction
——————————
——————————————-

[Gopa Kumar Sivadasan]

Hi

I am not sure why it is not working in service even after changing the browser settings. The only thing I can think…is the setting in Power Bi service language as shown below. Please check if in your service setting page, the ‘default browser language’ is selected as shown below:

?

——————————
Gopa Kumar
Limner Consulting

North Kerala User Group Leader
https://www.pbiusergroup.com/NorthKerala
——————————
——————————————-

[Vishesh Jain]

Hi ??,

That worked perfectly for the browser, but it is still not working for the mobile app.

I have changed the default language in the phone and changed the region to India.
I even downloaded the app from the Indian app store and it is still showing the numbers in million format.

If you have any more suggestions I’m all ears.

Thank you for responding.

——————————
Vishesh Jain
Owner
VR Construction
——————————
——————————————-