3 Replies Latest reply on Jun 20, 2018 7:50 AM by Mavis Liu

# adding millions "MN" or billions "BN" at the end of a number in calculated field

Hi,

I have a set of values for a particular dimension which need to be custom formatted using calculated field. so was using the below formula and it works fine to change the value to millions.

But, now I wanted to add "mn" or "bn" for the end user to understand whether the value is in millions or billions. so,how can I add the suffix or prefix to the below formula. please advise.

SUM(IF CONTAINS([KRI Name], "INDEBT_SEC") then ([KRI Value])/1000000

ELSEIF  CONTAINS([KRI Name], "INDEBT_UNSEC") then [KRI Value]/1000000 END)

thanks

Sowjanya

• ###### 1. Re: adding millions "MN" or billions "BN" at the end of a number in calculated field

Hi Sowjanya,

Please try this calculation and adapt it to yours:

if SUM([Sales])>1000000

then STR(round((SUM([Sales])/1000000),1) )+ 'M'

elseif SUM([Sales])>1000000000

then STR(round((SUM([Sales])/1000000000),1) )  + 'B'

else STR(round((SUM([Sales])/1000),1) )  + 'K'

END

Please see attached workbook.

Thank you,

Mavis

• ###### 2. Re: adding millions "MN" or billions "BN" at the end of a number in calculated field

hi,

thanks for sending me this. this is working, but unfortunately I cannot format the numbers as  ##,##.0.

otherwise this solution is working ok.

thanks

sowjanya

• ###### 3. Re: adding millions "MN" or billions "BN" at the end of a number in calculated field

Hi Sowjanya,

No you cannot because what I've created is a string and I've hard coded the values in there. So you can either update the hard coded values in the calculated fields or use an alternative method, which is:

I'll use thousands and millions as my example:

and

For each of these, set the default properties, e.g. millions into millions, thousands to thousands.

Then bring all these fields into text so that they're side by side:

Then you see this: