Please try this calculation and adapt it to yours:
then STR(round((SUM([Sales])/1000000),1) )+ 'M'
then STR(round((SUM([Sales])/1000000000),1) ) + 'B'
else STR(round((SUM([Sales])/1000),1) ) + 'K'
Please see attached workbook.
Millions and Billions.twbx 7.4 MB
thanks for sending me this. this is working, but unfortunately I cannot format the numbers as ##,##.0.
otherwise this solution is working ok.
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:
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:
Please find attached.
Millions and Billions.twbx 7.3 MB