Help with billions number formatting

[jburklund]

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.

[Anonymous]

These answers only work if you already know that you'll only have billions as a number, and if you only need text.
They don't work if you need to show $B, $M or $K depending on your values, and if you need to show that as a detail label on a pie chart.

[Anonymous]

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" ) )

)

[msharp]

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] ) )

)

[v-chuncz-msft]

@jburklund 

You may aslo try Custom format strings.

FORMAT ( [value], "$#,,,.00B" )

[jburklund]

Thank 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]

Try giving ## after decimal too

[jburklund]

Thanks but now I get $.01B

[amitchandak · Accepted solution]

I used this and I am getting $1.56B

Measure = format(1558753918,"$#,,,.##B")

Just make sure the data which you are formatting is number.

[EM679]

Can this be done to format percentages as well? I would like it to read 19.82% instead of 1982%

[jburklund]

Thank you @amitchandak this worked, I think the way the variable was coming through was messing it up but if I type in the number it works.