Changing Monetary Values to Numerical Abbreviations on X-Axis

[snightengale]

Hi, 

I'm building a scatter plot and have large values of money on the X-axis. I want to change those values from being $20,000,000,000 to $20B but can't seem to get that to work.

Measure:  Sum({<ModelDiscretionaryAcctFlag={'N'}>} AccountValue)

Number formatting: Money, $#,##0;-$#,##0

Tried using Number formatting: Custom Format: $#,##0.0a

Neither seems to give me what I want. Any thoughts? 

* 

[priscilarubim]

Hi @snightengale.

First, I would like to ask whether all the numbers are in the billions scale. If that is the case, you can write the formatting directly in the measure expression, but leave the chart number formatting set to Auto.

For example:

Num(
    Sum({<ModelDiscretionaryAcctFlag={'N'}>} AccountValue) / 1000000000,
    '$#,##0B;-$#,##0B'
)

This scales the value to billions and displays values like:

20,000,000,000 -> $20B
69,000,000,000 -> $69B

Hope this helps 🙂

[Chanty4u]

Try this 

Num(

    Sum({<ModelDiscretionaryAcctFlag={'N'}>} AccountValue)/1000000000,

    '$#,##0.0'

) & 'B'

[snightengale]

Hi @priscilarubim, 

Thank you for your help with this! My values are meant to be a drilldown, so I do need to adjust for millions and thousands. That did help, but I can't get that to work in a drilldown. I tried combining your measure with a bit of the logic from @Chanty4u and came up with this: 

If(
fabs(Sum({<ModelDiscretionaryAcctFlag={'N'}>} AccountValue)) >= 1000000000,

Num(
Sum({<ModelDiscretionaryAcctFlag={'N'}>} AccountValue) / 1000000000,
'$#,##0.0B;-$#,##0.0B'
),

If(
fabs(Sum({<ModelDiscretionaryAcctFlag={'N'}>} AccountValue)) >= 1000000,

Num(
Sum({<ModelDiscretionaryAcctFlag={'N'}>} AccountValue) / 1000000,
'$#,##0.0M;-$#,##0.0M'
),

If(
fabs(Sum({<ModelDiscretionaryAcctFlag={'N'}>} AccountValue)) >= 1000,

Num(
Sum({<ModelDiscretionaryAcctFlag={'N'}>} AccountValue) / 1000,
'$#,##0.0K;-$#,##0.0K'
),

Num(
Sum({<ModelDiscretionaryAcctFlag={'N'}>} AccountValue),
'$#,##0;-$#,##0'
)
)
)
)

BUT- that's changed the x-axis to values much lower than they should be for the top level. Any thoughts on fixing this?