Error with y-axis max scale adjustment

[pminnov]

Hi, 

I incorporated a formula to adjust the y-axis max value of a line chart based on the max value across a series of data points across years so that any data points in the graph don't extend beyond the y-axis max value. The formula calculates the average value for each year (which is what is being graphed), returns the max value and then mulitplies that max value by 20%, adds that calculated value to the total max value identified and then adjusts the max y-axis value in the chart to be that value (by adding 20% it ensures y-axis max value will always be above the max value identified). The formula works in almost all cases but in the odd scenario when data filters are applied it doesn't and I can' t figure out why (see below). I used the formula in a test variable minus the 20% calculation just to ensure the right max value is being selected and it worked (see screengrab below). Any ideas on what the issue is and how to fix it? 

This is the formula used to calculate the max y-axis value: 

Infra Users Y-axis Max Measure = VAR usersmaxaverage = { CALCULATE ( AVERAGE ( '2019-2022 PPR Data'[@14Researchadvancement#atinst] ), '2019-2022 PPR Data'[Reportingyear] = "2020" ), CALCULATE ( AVERAGE ( '2019-2022 PPR Data'[@14Researchadvancement#atinst] ), '2019-2022 PPR Data'[Reportingyear] = "2021" ), CALCULATE ( AVERAGE ( '2019-2022 PPR Data'[@14Researchadvancement#atinst] ), '2019-2022 PPR Data'[Reportingyear] = "2022" ), CALCULATE ( AVERAGE ( '2019-2022 PPR Data'[@14Researchadvancement#atinst] ), '2019-2022 PPR Data'[Reportingyear] = "2023" ), CALCULATE ( AVERAGE ( '2019-2022 PPR Data'[@14Researchadvancement#atinst] ), '2019-2022 PPR Data'[Reportingyear] = "2024" ), CALCULATE ( AVERAGE ( '2019-2022 PPR Data'[@14Researchadvancement#outsideinst] ), '2019-2022 PPR Data'[Reportingyear] = "2020" ), CALCULATE ( AVERAGE ( '2019-2022 PPR Data'[@14Researchadvancement#outsideinst] ), '2019-2022 PPR Data'[Reportingyear] = "2021" ),CALCULATE ( AVERAGE ( '2019-2022 PPR Data'[@14Researchadvancement#outsideinst] ), '2019-2022 PPR Data'[Reportingyear] = "2022" ), CALCULATE ( AVERAGE ( '2019-2022 PPR Data'[@14Researchadvancement#outsideinst] ), '2019-2022 PPR Data'[Reportingyear] = "2023" ), CALCULATE ( AVERAGE ( '2019-2022 PPR Data'[@14Researchadvancement#outsideinst] ), '2019-2022 PPR Data'[Reportingyear] = "2024" )  }

RETURN

MAXX( usersmaxaverage, [Value] )+MAXX(usersmaxaverage, [Value]*.2)

Thanks in advance!

[v-sdhruv]

Hi @pminnov ,
Just wanted to check if you had the opportunity to review the suggestions provided?
If the response has addressed your query, please accept it as a solution and give a 'Kudos' so other members can easily find it.
Thank You

[v-sdhruv]

Hi @pminnov ,
Just wanted to check if you had the opportunity to review the suggestions provided?
If the response has addressed your query, please accept it as a solution and give a 'Kudos' so other members can easily find it.
Thank You

[pminnov]

Hi, I've figured out the cause of the issue but I'm not sure how to address it within the DAX used to determine the y-axis max value. 

I recreated my initial chart using a subset of my original data that aligned to the slicer values that were selected. I then noticed that for the data column that influenced the y-axis max value, one of the data points was 0. When I replaced the 0 value with 1 the y-axis adjusted accordingly with the y-axis max being set 20% above the max value in the line graph (as per the DAX formula).

Below is the original data table. When the 0 value for ID 1003 under column “People Outside” is changed to 1, the y-axis behaves as it should given the DAX formula (also included below).

ID	Year	People Within	People Outside
1000	2022	3	4
1001	2022	4	3
1002	2023	10	33
1003	2023	0	0
1004	2024	2	25
1005	2024	2	5

People Y-axis Max Measure =

VAR peoplemaxaverage =

    {

        CALCULATE (

            AVERAGE ( 'Test Data'[People Within] ),

            'Test Data'[Year] = 2022

        ),

        CALCULATE (

            AVERAGE ( 'Test Data'[People Within] ),

            'Test Data'[Year] = 2023

        ),

        CALCULATE (

            AVERAGE ( 'Test Data'[People Within] ),

            'Test Data'[Year] = 2024

        ),

        CALCULATE (

            AVERAGE ( 'Test Data'[People Outside] ),

            'Test Data'[Year] = 2022

        ),

        CALCULATE (

            AVERAGE ( 'Test Data'[People Outside] ),

            'Test Data'[Year] = 2023

        ),

        CALCULATE (

            AVERAGE ( 'Test Data'[People Outside] ),

            'Test Data'[Year] = 2024

        )

    }

RETURN

    MAXX( peoplemaxaverage, [Value])*1.2

Original chart (with 0 value; y-axis max not working)

Updated chart (with 0 value replaced with 1; y-axis max working)

[v-sdhruv]

Hi @pminnov ,

The root cause is that a 0 value in your dataset is being considered the "maximum" in some filtered states, resulting in an incorrect or visually compressed y-axis, since multiplying  0 *1.2 still results in 0.

You can try using-

People Y-axis Max Measure =
VAR peoplemaxaverage =
FILTER(
{
CALCULATE ( AVERAGE ( 'Test Data'[People Within] ), 'Test Data'[Year] = 2022 ),
CALCULATE ( AVERAGE ( 'Test Data'[People Within] ), 'Test Data'[Year] = 2023 ),
CALCULATE ( AVERAGE ( 'Test Data'[People Within] ), 'Test Data'[Year] = 2024 ),
CALCULATE ( AVERAGE ( 'Test Data'[People Outside] ), 'Test Data'[Year] = 2022 ),
CALCULATE ( AVERAGE ( 'Test Data'[People Outside] ), 'Test Data'[Year] = 2023 ),
CALCULATE ( AVERAGE ( 'Test Data'[People Outside] ), 'Test Data'[Year] = 2024 )
},
[Value] > 0
)
RETURN
IF(
ISBLANK(MAXX(peoplemaxaverage, [Value])),
10, -- fallback y-axis max if everything is zero or blank
MAXX(peoplemaxaverage, [Value]) * 1.2
)

Hope this helps!
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[pminnov]

Hi, I tried your updated DAX formula but it did not work. The maxx formula is identifying the maximum average value across years which in the case of year 2023 in my data set would be (33 + 0)/2  = 16.5. I tried different things (changing the 0 value for the same ID in the other column, using the divide function in the DAX instead) but nothing worked. The only way to make the y-axis max respond appropriately is to change the 0 value in the 'people outside' column in the dataset. I don't understand why having a 0 value is causing this issue. 

[v-sdhruv]

Hi @pminnov ,

Yes, that would be better, to filter out 0s or Null if any.
The previous DAX was also an attempt to do the same.
If changing the value in 'People Outside' has helped you then please mark your answer
as "Accept as Solution" so that other users can benefit from it.

Thanks

[pminnov]

Hi @v-sdhruv, 

Unfortunately I can't alter the values in my dataset so that won't work as a solution. Also, I'm still unclear as to why having a 0 value in a column renders the y-axis max conditional formatting to stop working (even though the DAX formulas produce the intended result). 

[v-sdhruv · Accepted solution]

Hi @pminnov ,

Power BI’s conditional formatting for axis scales (like y-axis max) expects a scalar value from a measure. Even if your DAX produces a valid numeric scalar (like 19.8), if it depends on a table or a variable that becomes empty  in filtered contexts, Power BI may fail silently and ignore the formatting, falling back to auto-scaling the axis.

Hope this helps!
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[v-sdhruv]

Hi @pminnov ,
I am not sure about the nature of your data. The DAX was supposed to make the slicer interactive with your DAX.
However, I tried to re-produce the scenario assuming-
1.  '2019-2022 PPR Data'[@14Researchadvancement#atinst] is a numeric field
2. '2019-2022 PPR Data'[Reportingyear]  is numeric  as series of year as 2018,2019,2022,etc
and simply used 

Avg20% = MAXX('Table','Table'[Value])*1.2 as a measure to calculate 20% of the max value across each year.
Below is the result-

Example- year 2018- having max value 89,  will give you (89*1.2)=106.8.
Below Pbix for reference.

If you still face any issues, please consider sending a sample data and the expected output so that it will be easy for us to understand and provide a solution.

You can refer the following links to share the required info:

How to provide sample data in the Power BI Forum

And It is better if you can share a simplified pbix file. You can refer the following link to upload the file to the community.
How to upload PBI in Community
Thank you.

Thanks!
Best Regards
Shruti
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[v-sdhruv]

Hi @pminnov ,
You can wrap your DAX in Calculate() Remove Filter() functions for more compact DAX measure like-
CALCULATE(
AVERAGE('2019-2022 PPR Data'[@14Researchadvancement#atinst]),
REMOVEFILTERS('2019-2022 PPR Data'),
'2019-2022 PPR Data'[Reportingyear] = "2020"
)
 And repeat it for 10 entries in your list.
This ensures that row-level filters from slicers don't interfere, and each Calculate gets a clean evaluation with just the year filter.
Hope this helps!
If the response has addressed your query, please accept it as a solution and give a 'Kudos' so other members can easily find it.
Thank You

[pminnov]

The updated DAX code you suggested didn't adjust the y-axis max and made the chart do this: 

[lbendlin]

Looks benign.   You shouldn't calculate the value twice though, instead of 

RETURN

MAXX( usersmaxaverage, [Value] )+MAXX(usersmaxaverage, [Value]*.2)

use

RETURN

MAXX( usersmaxaverage, [Value])*1.2

Can you give us some sample data that shows the issue?

Btw, there is daxformatter.com that can bring your code into a more readable format