Forum Discussion

hanwang's avatar
hanwang
New Member
11 months ago
Solved

Dynamic formatting on bar charts

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's avatar
    Cookistador
    11 months ago

    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

     

1 Reply

  • Cookistador's avatar
    Cookistador
    Super User
    11 months ago

    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