Forum Discussion

deedoubleewe's avatar
deedoubleewe
New Member
10 months ago
Solved

Creating a dynamic y-axis on a clustered column chart with a legend

I have a clustered column chart (shown below) that graphs the Pct Value for each legend category (Legend) within each x-axis group (A and B).  I want to use a measure to create a dynamic y-axis maximum value to use as the range based on the maximum data value plus a little buffer rounded up to the next 20% axis interval. However, the maximum value being calculated is not showing up correctly. The furthest right dark bar (5+ times for group B) is 73.3%, so I want the y-axis to go up to 80%; however, it's only going up to 60% (assuming it's basing its calculation off of the group A cluster of columns).

 

My DAX measure to calculate what the y-axis maximum should be is:

YAxis Max = CEILING(MAXX(VALUES(Table[Legend]),[Value])+.05,.2))

 

Thanks for the insight!

Need Help
  • OwenAuger's avatar
    OwenAuger
    10 months ago

    Hi deedoubleewe 

    The immediate issue appears to be that the maximum value should be determined at the granularity of the Legend & the X-Axis field combined.

     

    Assuming the field on the X-Axis is Table[Category], something like this should work as expected:

    YAxis Max =
CEILING (
    MAXX (
        SUMMARIZECOLUMNS ( Table[Legend], Table[Category], "@Value", [Value] ),
        [@Value]
    ) + .05,
    0.2
)

     

    You can debug or verify that YAxis Max is returning the expected value by displaying it on a separate Card visual (subject to the same filters applying to the original visual).

     

    Does something like this work for you?

4 Replies

  • OwenAuger's avatar
    OwenAuger
    Icon for Super User rankSuper User
    10 months ago

    Hi deedoubleewe 

    The immediate issue appears to be that the maximum value should be determined at the granularity of the Legend & the X-Axis field combined.

     

    Assuming the field on the X-Axis is Table[Category], something like this should work as expected:

    YAxis Max =
CEILING (
    MAXX (
        SUMMARIZECOLUMNS ( Table[Legend], Table[Category], "@Value", [Value] ),
        [@Value]
    ) + .05,
    0.2
)

     

    You can debug or verify that YAxis Max is returning the expected value by displaying it on a separate Card visual (subject to the same filters applying to the original visual).

     

    Does something like this work for you?

    • deedoubleewe's avatar
      deedoubleewe
      New Member
      10 months ago

      OwenAuger That worked perfectly! I figured I needed some combination of the variables, but couldn't quite get it right. This is exactly what I needed. Thanks 🙂

  • Ilgar_Zarbali's avatar
    Ilgar_Zarbali
    Icon for Super User rankSuper User
    10 months ago

    You’re getting 0.60 because the measure is being evaluated in the context of each bar (i.e., one Group and one Legend at a time).
    MAXX(VALUES(Table[Legend]), [Value]) only sees the current legend (and, due to the x-axis, the current group), so the max is computed from a reduced set—often only the bars in Group A.

    Create the max from a context that keeps slicers but removes the visual’s axis and legend filters, then round it:

     

    YAxis Max :=
    VAR MaxAcrossVisual =
    MAXX (
    // respect external slicers but ignore the visual's Group & Legend
    SUMMARIZE (
    ALLSELECTED ( 'Table' ),
    'Table'[Group],
    'Table'[Legend]
    ),
    CALCULATE ( [Value] )
    )
    RETURN
    CEILING ( MaxAcrossVisual + 0.05, 0.2 )

     

     

    Equivalent shorter form:

     

    YAxis Max :=
    VAR m =
    CALCULATE (
    MAXX ( ALL ( 'Table'[Group], 'Table'[Legend] ), [Value] ),
    // ALL removes just the axis & legend filters; slicers on other columns persist
    KEEPFILTERS ( ALLSELECTED ( 'Table' ) )
    )
    RETURN CEILING ( m + 0.05, 0.2 )

     

     

    Bind YAxis Max to the Y-axis → Range → Max (fx).
    Now the chart will find the true max across all groups and legend series in the visual (73.3% → rounded to 80%).

     

    I hope it helps.

  • v-ssriganesh's avatar
    v-ssriganesh
    Icon for Community Support rankCommunity Support
    10 months ago

    Hi deedoubleewe,
    Thank you for reaching out to the Microsoft fabric community forum.

    I reproduced your scenario in Power BI Desktop and confirmed that the issue occurs because the Y-axis max measure was only considering the visible subset of data.

    By using the below DAX, the Y-axis now dynamically scales to include the overall maximum across all groups and legends:

    YAxis Max =

VAR _MaxValue = MAXX(ALLSELECTED(TableName), [Value])

RETURN CEILING(_MaxValue + 0.05, 0.2)

     

    This correctly extends the Y-axis to 80% when your highest bar (Group B – 5+ times) is 73.3%.

     

    For your reference, I’ve attached a sample .pbix showing the expected output. If I misunderstand your needs or you still have problems on it, please feel free to let us know. 

    Thank you, Ilgar_Zarbali & OwenAuger for sharing valuable insights and your continued contribution in the community

    Best regards,
    Ganesh Singamshetty.