Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago

Dynamic format for measure on column/bar chart

I've been trying to set dynamic format for measure on bar/column chart, but when data labels' display units is set to "none", Power BI ignores my dynamic format UNLESS I set decimal places to > 0 – t...
Reports
  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    Here is the sample data:

    CustomerSales
    Customer 1123
    Customer 212345
    Customer 31234567

     

    Here is the sample measure:

     

    Sum of Sales = 
SUM('Table'[Sales])

     

     

    Here is the format string for the measure:

     

    VAR __AbsVal = ABS(SELECTEDMEASURE())
RETURN
SWITCH(
    TRUE(),
    __AbsVal < 1000, "#,0",
    __AbsVal < 1000000, "#,0,.K",
    __AbsVal < 1000000000, "#,0,,.M",
    "#,0,,,.B"
)

     

     

    This is what I expect:


    This is what I see on the bar chart:

     

    • lbendlin's avatar
      lbendlin
      Icon for Super User rankSuper User
      3 years ago

       

      SWITCH(
    TRUE(),
    SELECTEDMEASURE() < 1000, "#",
    SELECTEDMEASURE() < 1000000, "#,##0,.0 K",
    SELECTEDMEASURE() < 1000000000, "#,##0,,.0 M",
    "#,##0,,,.0 B"
)

       

      this should get you closer.  Still has one decimal, but I'm sure they will address this bug soon:  Dynamic Format Strings Bug - Microsoft Power BI Community

      • Anonymous's avatar
        Anonymous
        Not applicable
        3 years ago

        I know it is a bug, that is why I originally posted this in the "Issues" forum, but somebody moved it here, without any notice.

        I also know adding one decimal place helps (as I mentioned in my first post), but I would like to be able to set a dynamic format to 0 decimal places and use it on bar/column chart. As for now it's just not possible.

        Nevertheless, thank you for trying to help, much appreciated.