Forum Discussion
jburklund
Helper III
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 funct...
I used this and I am getting $1.56B
Measure = format(1558753918,"$#,,,.##B")Just make sure the data which you are formatting is number.
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"
RETURN
IF (
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.
RETURN
IF (
number_ >= 1000000000,
FORMAT ( number_ / 1000000000, decimal & "B" ),
IF ( number_ >= 1000000, FORMAT ( number_ / 1000000, decimal & "M" ), SUM ( promotions_performance_detailed[item_sales] ) )
)