Forum Discussion

powerbiexpert22's avatar
powerbiexpert22
Icon for Impactful Individual rankImpactful Individual
16 days ago
Solved

dynamic format string causing incorrect Y scale

i have applied dynamic format string in my semantic model however the Y axis scale is showing repeated value

 

 

 

 

Need Help
power bi
  • jubinsoni's avatar
    jubinsoni
    16 days ago

    Hi powerbiexpert22​,

    The problem is in the format string expression itself. It returns FORMAT(SelectedMeasure, ...), which is the already-formatted value (for example "12.3M"), not a format pattern.

    Power BI then uses that text as the pattern for every number on the axis. Characters that aren't format placeholders are printed literally, so every tick shows the same "12.3M". That is the repeated value you are seeing.

    current:  format string expr -> FORMAT(value, "#,0.0,,M") -> "12.3M"
          axis tick 0, 5M, 10M, 15M -> each rendered with pattern "12.3M"
          -> "12.3M", "12.3M", "12.3M" ...
fix:      format string expr -> "#,0.0,,\"M\""
          axis tick 5000000 -> "5.0M", 10000000 -> "10.0M"

    Return only the pattern, and use SELECTEDMEASURE() so it works no matter which measure the calc item or field parameter switches to:

    VAR v = ABS ( SELECTEDMEASURE () )
RETURN
    SWITCH (
        TRUE (),
        v < 1e3, "#,0",
        v < 1e6, "#,0.0,\"K\"",
        v < 1e9, "#,0.0,,\"M\"",
        "#,0.0,,,\"B\""
    )

    Each comma after the 0 divides by 1,000. Literal letters belong in escaped quotes so K/M/B aren't read as format codes.

    One more thing to expect: the axis uses a single format string for the whole axis, evaluated at the visual (total) level. If the total is in millions, all ticks show in M, even the small ones. That is usually what you want. If not, set the Y axis Display units manually and the data labels keep the dynamic format.

    So my overall approach would be:

    format string expression returns a pattern, never FORMAT()
-> use SELECTEDMEASURE() + escaped "K"/"M"/"B" suffixes
-> axis follows the total-level pattern; data labels per point
-> override Y axis display units if the scale still looks off

    AI-assisted drafting: AI was used to help structure and phrase this response. I reviewed and validated the technical content before posting.

5 Replies

  • maruthisp's avatar
    maruthisp
    Icon for Super User rankSuper User
    16 days ago

    Hi powerbiexpert22​,

    This normally happens when the Y-axis Display units are still set to Auto.

    Your dynamic format string changes how the measure is displayed, but the chart can still apply its own K/M/B scaling on top of that.

    Try this first:

    1. Select the chart.
    2. Go to Format visual → Y-axis → Values.
    3. Set Display units = None.
    4. If you are also using data labels, go to Data labels → Values → Display units and set that to None as well.

    Keep your actual measure numeric. For example:

    Total Sales = SUM ( FactSales[SalesAmount] )

    Then apply the dynamic format string separately, for example:

    VAR _Value = ABS ( [Total Sales] ) RETURN SWITCH ( TRUE(), _Value >= 1000000, "#,0.0,, ""M""", _Value >= 1000, "#,0.0, ""K""", "#,0" )

    This should be configured as the Dynamic format string of the measure, not as another measure using FORMAT().

    The reason is that FORMAT() converts the result to text, whereas a dynamic format string keeps the measure numeric, so the Y-axis can still calculate the scale correctly.

    Best Regards,
    Maruthi
    LinkedIn - http://www.linkedin.com/in/maruthi-siva-prasad/
    X            -  Maruthi Siva Prasad - (@MaruthiSP) / X

  • jubinsoni's avatar
    jubinsoni
    Icon for Solution Sage rankSolution Sage
    16 days ago

    Hi powerbiexpert22​,

    The problem is in the format string expression itself. It returns FORMAT(SelectedMeasure, ...), which is the already-formatted value (for example "12.3M"), not a format pattern.

    Power BI then uses that text as the pattern for every number on the axis. Characters that aren't format placeholders are printed literally, so every tick shows the same "12.3M". That is the repeated value you are seeing.

    current:  format string expr -> FORMAT(value, "#,0.0,,M") -> "12.3M"
          axis tick 0, 5M, 10M, 15M -> each rendered with pattern "12.3M"
          -> "12.3M", "12.3M", "12.3M" ...
fix:      format string expr -> "#,0.0,,\"M\""
          axis tick 5000000 -> "5.0M", 10000000 -> "10.0M"

    Return only the pattern, and use SELECTEDMEASURE() so it works no matter which measure the calc item or field parameter switches to:

    VAR v = ABS ( SELECTEDMEASURE () )
RETURN
    SWITCH (
        TRUE (),
        v < 1e3, "#,0",
        v < 1e6, "#,0.0,\"K\"",
        v < 1e9, "#,0.0,,\"M\"",
        "#,0.0,,,\"B\""
    )

    Each comma after the 0 divides by 1,000. Literal letters belong in escaped quotes so K/M/B aren't read as format codes.

    One more thing to expect: the axis uses a single format string for the whole axis, evaluated at the visual (total) level. If the total is in millions, all ticks show in M, even the small ones. That is usually what you want. If not, set the Y axis Display units manually and the data labels keep the dynamic format.

    So my overall approach would be:

    format string expression returns a pattern, never FORMAT()
-> use SELECTEDMEASURE() + escaped "K"/"M"/"B" suffixes
-> axis follows the total-level pattern; data labels per point
-> override Y axis display units if the scale still looks off

    AI-assisted drafting: AI was used to help structure and phrase this response. I reviewed and validated the technical content before posting.

  • ShivekMaharaj's avatar
    ShivekMaharaj
    Icon for Community Champion rankCommunity Champion
    15 days ago

    Hi powerbiexpert22​,

    The repeated Y-axis value is most likely coming from the dynamic format string expression itself.

    In a dynamic format string, the expression should return the format pattern, not the already formatted measure value.

    At the moment, you are using FORMAT(SelectedMeasure, ...), so for a value such as 2,877,000 the expression can effectively return something like "2877K". Power BI then treats that result as the format string, which is why the same text can appear repeatedly on the Y-axis.

    Try returning the format strings directly instead, for example:

    VAR SelectedMeasure = [PCD YTD Actual]

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

    Microsoft shows the same pattern in the SELECTEDMEASURE documentation, where the dynamic format expression returns the format string itself rather than using FORMAT() to return the displayed value.

    I would also check the visual's Y-axis Display units setting and set it to None while testing, because visual-level autoscaling can also affect how custom format strings are displayed. Microsoft documents that interaction in the dynamic format string guidance.

    Your underlying measure can remain numeric; only the format string logic needs to change.

    AI-assisted drafting: AI was used to help structure and phrase this response. I reviewed and validated the technical content before posting.

  • v-sathmakuri's avatar
    v-sathmakuri
    Icon for Community Support rankCommunity Support
    2 days ago

    Hi powerbiexpert22​ ,

    Could you review the suggestion provided above and let us know if you have any additional questions, we are happy to address. 

    Thanks!!