Forum Discussion
Helper IIError with y-axis max scale adjustment
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:
Thanks in advance!
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.Update: I ran into the same issue with the same type of visual again but with slightly different data. I used co-pilot, uploaded some of my data and it eventually explained that Power BI’s Axis Auto-Scaling is “nice number” based. Power BI does not always set the axis max to the highest data value. Instead, it chooses a “nice” round number that is just above or near the highest value, but sometimes it can be slightly below if the highest value is close to a round number. The charting engine tries to optimize for readability and gridline spacing. If the highest value is only slightly above a round number, Power BI may still use the lower round number for the axis max. If the highest value is above the axis max, Power BI will still plot the point, but it will appear at the top edge of the chart, sometimes even slightly above the last gridline. This can make the chart misleading, as it looks like the axis max is lower than the highest data point. If Power BI’s auto-scaling logic decides that a value is “good enough” for the axis max, it will ignore your conditional formatting unless the returned max value in your measure (the max value in the visual multiplied by some number to return the value to be used as the y-axis max value) is significantly higher than the auto-scale value or unless the chart type/visualization engine allows for more flexibility. The solution is to either use a higher multiplier in the DAX formula (the value that is multiplied against the MAX value) or use a DAX formula such as:
Y-axis Max Measure =
VAR MaxValue = [Your Max Calculation]
VAR NextRound =
CEILING ( MaxValue, 1 )
RETURN
NextRoundThere are trade-offs to both approaches but this does fix the problem I posted about.
13 Replies
- lbendlin
Super User
Looks benign. You shouldn't calculate the value twice though, instead of
RETURNMAXX( usersmaxaverage, [Value] )+MAXX(usersmaxaverage, [Value]*.2)useRETURNMAXX( usersmaxaverage, [Value])*1.2Can 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 
v-sdhruvCommunity Support
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
pminnovThe updated DAX code you suggested didn't adjust the y-axis max and made the chart do this:
- 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 usedAvg20% = MAXX('Table','Table'[Value])*1.2 as a measure to calculate 20% of the max value across each year.
Below is the result-
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.Example- year 2018- having max value 89, will give you (89*1.2)=106.8.
Below Pbix for reference.
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. - 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.
