Convert number to Billions in DAX

How to convert a number into billions with 2 decimal places using DAX

Hello @Anu,

Thank You for posting your query onto the Forum.

You can use the FORMAT function to convert the figures. For example to convert the numbers into thousands ('000) write the expression as follows -

FORMAT( [value] , "0,000.00")

OR

FORMAT( [value] , "#,###.##")

Similarly, follow the approach to convert the figures into the billions.

But just remember once you use the FORMAT function the number gets converted into the text format because we’ve used symbol in it i.e. (comma as a separator).

Hoping you find this useful and meets your requirements that you’re looking for. :slightly_smiling_face:

Thanks and Warm Regards,
Harsh

I understand the format function can be used, for thousand seperator etc, but i want it to look like “2.13B” say, I am not able to get this particular formatting.

Hello @Anu,

Can you please try the below formula in your file?

Format = FORMAT( [value] , "$ 0,,,.##B" )

I’m providing the screenshot below of the result as well as attaching the working of the PBIX file for reference.

Hoping this helps you. :slightly_smiling_face:

Thanks and Warm Regards,
Harsh

Billions Format.pbix (661.6 KB)

1 Like

Also if i want to format % without decimal places, how to do? using dax.

Hello @Anu,

Can you please provide an example for it?

Thanks and Warm Regards,
Harsh

You can refer to the documentation here:

https://docs.microsoft.com/en-us/dax/pre-defined-numeric-formats-for-the-format-function

at the end of the article there are more links you can follow to discover other formatting options using the FORMAT() function in DAX.

I hope this is helpful.

If i use Format (value,“Percent”) it will give 97.45%
But i only want 97%

Hi @Anu,

If you follow this link which was at the bottom of the documentation page, you will learn about custom User-defined numeric formats.

https://docs.microsoft.com/en-us/dax/custom-numeric-formats-for-the-format-function

I believe this should do it.
FORMAT(value, "#%")

Could you explain a bit, why are 2 commas used?

Hello @Anu,

Well actually to convert the numbers into the billions I had put 3 commas and a full stop as a decimal separator. For converting them into millions use only 2 commas and for thousands use only a single comma.

I’m providing a screenshot below of the results that I’ve mentioned.

I’m also providing a link that I used as an reference that provides an explanation about formatting the numbers (although they’re used in the Excel) but same concept was applied here as well). The only thing is they’ve applied it directly under the Formatting tab in Excel so they’ve included words under the quotes and have excluded 0 and commas from it.

Lastly, also attaching the PBIX file of the working as well as providing a link below of the similar type of query asked onto the Power BI Community Forum.

Hoping you find this useful and and helps you in better understanding about the formatting of numbers. :slightly_smiling_face:

Thanks and Warm Regards,
Harsh

Billions Format - Harsh.pbix (662.3 KB)

https://www.extendoffice.com/documents/excel/3771-excel-format-numbers-in-millions.html

1 Like