Forum Discussion

hasarinfareeth's avatar
hasarinfareeth
Frequent Visitor
1 year ago

Dynamically changing Y axis limit for cluster chart visual

Hi - I have created a custom chart having clustered, staked and line charts by combined 3 visual as shown in the below image. To aling all the 3 visuals, I have set the Y axis limit to 2000, a constant limit . I have a slicer which changes the values in the below chart. for certain filter, the values are too low, therefoe having a higher limit of the Y axis is not useful in terms of visualizing the total received, Total Closed, Carried Forward and Backlog closed count. Is there a way to change the Y axis range dynamically based on the Total values for certain filters in the slicer?

 

When slicer has all the value selected, 

When silcer has some value selected, 

 

Thanks

Fareeth

 

Need Help

3 Replies

  • Bibiano_Geraldo's avatar
    Bibiano_Geraldo
    Super User
    1 year ago

    Hi hasarinfareeth ,

    You can achieve your goal by this DAX:

    MaxLimit = 
VAR MaxValue = 
MAX(
    MAX(
        [total received],
        [Total Closed]
    ),
    MAX(
        [Carried Forward],
        [Backlog closed]
    )
)
RETURN
MaxValue * 1.1

     

    Now select your chart and go to format pane, and in your Y-Axis select the fx to apply maximium limit:

     

    Now the field should be based on the creatd measure:

     

    Now your max will be dynamic and will be based on maximum of your measures increased in 10% (1.1).

     

     

    • hasarinfareeth's avatar
      hasarinfareeth
      Frequent Visitor
      1 year ago

      Thank you very much Bibiano_Geraldo but this is not giving me an expected result as one of the value in the visual is a count of total rows.

      Tried with the below. 

      MaxLimit =
      VAR MaxValue =
      MAX(
          MAX(
              [Total Created Ticket],
              COUNT('Service IT Closed Tickets'[TicketNumber])
          ),
          MAX(
              [Backlog Carried Forward Count],
              [Backlog Closed Count]
          )
      )
      RETURN
      MaxValue * 1.1
       
      [Total Created Ticket] = Ticket created + Backlog carry forwarded
       
      The Y axis maximum limit is exceeding the way beyond the total number of received tickets per month. I have an average of around 1800 tickets received per month, so I set the maximum limit as 2000 but it is not useful if I use filter values.

       

      • Anonymous's avatar
        Anonymous
        Not applicable
        1 year ago

        Hi hasarinfareeth, hello Bibiano_Geraldo, thank you for your prompt reply!

        According to your screenshot, your chart only shows four key metrics, why does your MaxLimit calculation include values that are not part of the actual chart?

        If possible, please upload the sample pbix file without any sensitive information for further troubleshooting.