Dynamic formatting on bar charts

[hanwang]

Hi, 

I am facing an issue on bar charts. I have 3 bars would like to show on the bar chart. One of them is in $300 billion, one of them is in -$300 billion and one in $80 million. In the bar chart, I see it automatically converted into $0.3T, -$0.3T and $0T. Is there any way can change the display to when it is a trillion, show billion and when it is billion show billion, when it is million show million, when is thounsand show k.

I tried to use the actual value in the bar and dynamic formatting as: 

SWITCH (
   TRUE(),
   ABS(v) >= 1e12, FORMAT( v/1e9, "$#,0.0") & "Bn", 
   ABS(v) >= 1e9,  FORMAT( v/1e9, "$#,0.0") & "Bn",
   ABS(v) >= 1e6,  FORMAT( v/1e6, "$#,0.0") & "M",
   ABS(v) >= 1e3,  FORMAT( v/1e3, "$#,0.0") & "K",
   FORMAT( v, "$#,0")
)

But it doesn't work when it has multiple bars.

Can someone share some ideas?

Thanks.

[Cookistador · Accepted solution]

Hello @hanwang 

For me , the following approach works:

Value Formatted =

VAR v = SUM('Table'[Value])

RETURN

    SWITCH (

        TRUE(),

        ABS(v) >= 1e9,  FORMAT( v / 1e9, "$#,0.0 Bn" ),

        ABS(v) >= 1e6,  FORMAT( v / 1e6, "$#,0.0 M" ),

        ABS(v) >= 1e3,  FORMAT( v / 1e3, "$#,0 K" ),

        FORMAT( v, "$#,0" )

    )

And then in Format your visual in data label and values, I replaced the standard one by the measure

If it is not working, can you share more details like the look and fields you would like at the end

To achieve that, I created the following measure