Forum Discussion
Helper IIIHelp with billions number formatting
I am trying to format the number $1,558,753,918 to read $1.56B
I would use the built in billions formatting but it uses "bn" instead of "B" after a number.
I have tried using the format function but the closest I got was $1B.
Thanks for your thoughts.
I used this and I am getting $1.56B
Measure = format(1558753918,"$#,,,.##B")Just make sure the data which you are formatting is number.
10 Replies
v-chuncz-msftCommunity Support
jburklundThank you v-chuncz-msft but this still just gets me to $1B. I need it to also show to the nearest millions, i.e. $1.56B.
- amitchandak
Super User
Try giving ## after decimal too
- You can create a new measure like this. The output data type will be text. Hope this works !Item Sales B =VAR number_ =SUM ( promotions_performance_detailed[item_sales] )VAR decimal = "0.0"RETURNIF (number_ >= 1000000000,FORMAT ( number_ / 1000000000, decimal & "B" ),IF ( number_ >= 1000000, FORMAT ( number_ / 1000000, decimal & "M" ) ))
This helped me a lot so thank you.
I added one piece to it to make it more dynamic if the value isnt in millions or billions. Just an 'else' clause to use the original column.
Changing the value, specifically the number of trainiling zero's, of 'VAR decimal = "0.0' allows you to change the number of decimals displayed.
Item Sales B =VAR number_ =SUM ( promotions_performance_detailed[item_sales] )VAR decimal = "0.000" -- Editing this allows for more decimal places, currently set to 3.RETURNIF (number_ >= 1000000000,FORMAT ( number_ / 1000000000, decimal & "B" ),IF ( number_ >= 1000000, FORMAT ( number_ / 1000000, decimal & "M" ), SUM ( promotions_performance_detailed[item_sales] ) ))
