Forum Discussion

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

dynamic format string multiple measures

i am trying to use dynamic format string feature to control display units on line and clustered bar chart, below is the sample data. i want to show units based on maximum value by month (not total) o...
Need Help
  • rajendraongole1's avatar
    rajendraongole1
    17 days ago

    Hi powerbiexpert22​ - Your format string logic is fine. The labels show M because the format isn’t being applied at all, so the visual is auto-scaling. There are three things to fix:

    1. Turn off auto display units on the visual
      In the line and clustered column chart, set Display units = None for:
    • Data labels → Values (for all series)
    • Y-axis, and the secondary Y-axis if you use one

    When this is left on Auto, it overrides dynamic format strings.

    1. Commit the format expression on each measure
      The empty formula bar means the expression wasn’t saved. For each of the three measures, select the measure and set Format = Dynamic. Then make sure the formula bar dropdown shows Format, paste the expression, and press Enter. Dynamic format strings are stored per measure, so you need to repeat this for all three. I’d also escape the K and M characters:

    VAR _m = [VMAX] RETURN SWITCH ( TRUE (), _m < 1E3, "#,##0", _m < 1E6, "#,##0,.0\K", "#,##0,,.00\M" )

    3. Check the VMAX calculation
    VMAXCYPLANUSD returns 639,267, but the largest monthly CY Plan value in your table is 199,960. The calculation is probably using ALL(), which removes your year/slicer filters. Try ALLSELECTED over the axis column together with its sort column:

    VMAX = MAXX ( ALLSELECTED ( 'Date'[Month], 'Date'[MonthNo] ), MAX ( MAX ( [test_cyactualusd], [test_pyactualusd] ), [test_fyplan] ) )

    Once these are in place, all labels should show in K for your current data. If the max goes above 1M, the labels switch to M together.

    If this helps, please mark it as the solution so others can find it.

grazitti_sapna's avatar
grazitti_sapna
Icon for Super User rankSuper User
17 days ago

Hi powerbiexpert22​,

Issue with your DAX is Dynamic format strings in Power BI must return a valid text string. If your VMAX variable evaluates to BLANK(), the expression fails and Power BI discards the dynamic format formula.

For a line chart showing  values in million issue, Line and Clustered Bar charts default Display units to Auto or Millions in the formatting pane. This visual-level property overrides custom DAX dynamic format strings and forces values back to millions.

Update your DAX as below:-

VAR Val = [VMAX]
RETURN
    SWITCH(
        TRUE(),
        ISBLANK(Val), "#,##0",
        Val < 1000, "#,##0",
        Val < 1000000, "#,##0,.0""K""",
        "#,##0,,.0""M"""
    )

It'll fix your table data issue, for Clusterd bar chart issue, follow below:-

  • Select the chart visual.
  • Go to Format visual > Visual tab > Data labels (and Y-axis) > Values.
  • Set Display units to None.

🌟 I hope this solution helps you unlock your Power BI potential! If you found it helpful, click 'Mark as Solution' to guide others toward the answers they need.
💡 Love the effort? Drop the kudos! Your appreciation fuels community spirit and innovation.
🎖 As a proud SuperUserand Microsoft Partner, we’re here to empower your data journey and the Power BI Community at large.
🔗 Curious to explore more? [Discover here].
Let’s keep building smarter solutions together!