Max y-axis based on field parameter selected

[nleuck_101]

Hello All,

I could use some help getting the max y-axis from the selected field parameter slicer. I don't like that the y-axis is showing 800 when the total is over 800. I would pefer it be 1,000 in this scenario. 

When I change my field parameter to my next slicer it looks a little better but it's still over the number shown in the y-axis.

I basically need a dynamic y-axis that gets the max value based on the field parameter selected. Any help would be greatly appreciated.

[parry2k]

@nleuck_101 if you need help you have to do some work at your end. Create a sample data and share that instead of sharing the actual power bi file. If you cannot do this, I guess not sure how I can help. Good luck!

[parry2k]

@nleuck_101 share pbix file using one drive/google drive . Remove any sensitive information before sharing.

[nleuck_101]

@parry2k 

I can't share anything from this report.

[parry2k]

@nleuck_101 here you go, change table and column name as per your model:

Measure = 
--parameter table name and parameter column name you are using on slicer
VAR __SelectedParameter = MAXX ( ALLSELECTED ( Parameter ), [Parameter] )
//this would be table and column that you are using on x-axis, 
//for example if it is a year column from date table then this would be
//ALLSELECTED ( 'Date'[Year] )
VAR __Table = ALLSELECTED ( 'Calendar'[Month Sort], 'Calendar'[Month] )
RETURN
//so depedning on the paramter name selected, you just switch to that measure
//in my case I have paramter with two name Qty and Sales and these correspond to 
//[Qty] and [Sales] measure, make changes to switch statement as per your model
SWITCH ( 
    __SelectedParameter,
    "Qty", MAXX ( __Table, [Qty] ),  
    "Sales", MAXX ( __Table, [Sales] )
)

[nleuck_101]

@parry2k 

This did not change anything. My Y-axis values look just like my screenshot.

[Ashish_Mathur]

Hi,

Share the download link of the PBI file with your viz already set up.

[nleuck_101]

@Ashish_Mathur 

I can not share a link to this file.

[v-prasare]

You can handle this by using SELECTEDVALUE() on your field parameter table and conditionally computing the max based on which measure is active.

Step 1. Create your field parameter (which you already have):

Measure Parameter =

{ ("Name1", NAMEOF([Measure1]), 0),

    ("Name2", NAMEOF([Measure2]), 1),

    ("Name3", NAMEOF([Measure3]), 2) }

Step 2. Create a new measure for Dynamic Y-Axis Max:

Dynamic Y-Axis Max =

VAR SelectedMeasure = SELECTEDVALUE('Measure Parameter'[Name])

VAR MaxValue = SWITCH(  SelectedMeasure, "Name1",

MAXX(ALLSELECTED('YourMainDataTable'), [Measure1]), "Name2",

MAXX(ALLSELECTED('YourMainDataTable'), [Measure2]), "Name3",

            MAXX(ALLSELECTED('YourMainDataTable'), [Measure3]) )

VAR PaddedValue = MaxValue * 1.15

VAR RoundedValue =

    SWITCH(

        TRUE(),

        MaxValue = 0, 10,

        PaddedValue > 500, ROUNDUP(PaddedValue, -2),

        PaddedValue > 100, CEILING(PaddedValue, 50),

        PaddedValue > 10, CEILING(PaddedValue, 10),

        CEILING(PaddedValue, 5) )

RETURN RoundedValue

Use this measure to drive the Y-axis in your chart

[nleuck_101]

@v-prasare 

This did not work. Now my axis is only going up to ten.

[v-menakakota]

Hi @nleuck_101 ,

As I am also a member of the CST team, I wanted to follow up regarding the issue.

I hope the information provided helps resolve your issue. If you have any further questions or need additional assistance, please feel free to contact us. We are here to help.

Best Regards, 
Community Support Team

[v-menakakota]

Hi @nleuck_101 ,

As I am also a member of the CST team, I wanted to follow up regarding the issue.

I hope the information provided helps resolve your issue. If you have any further questions or need additional assistance, please feel free to contact us. We are here to help.

Best Regards, 
Community Support Team

[DataNinja777]

Hi @nleuck_101 ,

You can absolutely create a dynamic Y-axis that adjusts to your field parameter. The solution is to write a single DAX measure that calculates an appropriate maximum value based on the data visible in the chart. This measure will then be used to control the Y-axis range in your chart's formatting options.

First, you'll need to create a new measure in Power BI. This DAX code finds the highest value among the currently visible bars, adds a 15% buffer for headroom, and then intelligently rounds that value up to a clean, readable number. For instance, a max value of 860 will result in an axis of 1000, and a max value of 123 will result in an axis of 150.

Dynamic Y-Axis Max = 

// Step 1: Find the maximum value currently displayed on the chart
VAR MaxValue =
    MAXX(
        ADDCOLUMNS(
            SUMMARIZE(
                ALLSELECTED(YourMainDataTable),      // <-- 1. CHANGE to your main data table name
                YourXAxisColumn,                     // <-- 2. CHANGE to your X-axis column
                YourLegendColumn                     // <-- 3. CHANGE to your Legend column (e.g., 'Table'[Year])
            ),
            "@ChartValue", [Your Field Parameter Measure] // <-- 4. CHANGE to your measure
        ),
        [@ChartValue]
    )

// Step 2: Add a buffer (e.g., 15%) and round up to a "nice" number
VAR PaddedValue = MaxValue * 1.15 // Adjust 1.15 for more/less padding

VAR RoundedValue =
    SWITCH(
        TRUE(),
        MaxValue = 0, 10, // Handles case where max is 0 to prevent errors
        PaddedValue > 500, ROUNDUP(PaddedValue, -2), // Rounds up to the nearest 100
        PaddedValue > 100, CEILING(PaddedValue, 50),   // Rounds up to the nearest 50
        PaddedValue > 10, CEILING(PaddedValue, 10),    // Rounds up to the nearest 10
        CEILING(PaddedValue, 5)                        // Rounds up to the nearest 5
    )

RETURN
    RoundedValue

Before this code will work, you must replace the four placeholders with the actual names from your data model. YourMainDataTable should be the name of your primary data table. YourXAxisColumn is the column on the chart's X-axis. YourLegendColumn is the field used in the Legend area, which is Year in your case. Finally, [Your Field Parameter Measure] is the measure being displayed by your field parameter; if you dragged the parameter itself to the Y-axis field well, this would be the name of that parameter measure (e.g., [Parameter]).

After creating the measure, you need to apply it to your visual. Select the bar chart, then go to the Format your visual pane (the paintbrush icon 🖌️). Expand the Y-axis section, and under Range, find the Maximum value box. Click the conditional formatting (fx) button next to it. In the dialog that opens, set the Format style to Field value and select your new Dynamic Y-Axis Max measure as the field. Once you click OK, your chart's Y-axis will automatically resize based on your slicer selections, giving you a clean visual every time.

Best regards,

[nleuck_101]

Hello @DataNinja777 ,

Thank you for your response. I have an issue though, in the formula where you change the value to "your field paramter measure", mine is not a measure, it's a combination of 3 measures. The column that I created from the field parameter is actually 3 different measures. I use the column created by the field parameter select one of the measures to change the charts. Below is my DAX:

Table = {("Name1", NAMEOF([Measure1]), 0), ("Name2", NAMEOF([Measure2]), 1), ("Name3", NAMEOF([Measure3]), 2)