Forum Discussion
Dynamic Y range visual calculation in two level hierarchy
Hi somogyitamas88 ,
Could you kindly provide a small sample of your data, ensuring that it contains only the date, Sales, and Sales Plan fields, with no sensitive information included.
Additionally, please specify the Y axis values you would like to see at both the month and week levels so I can accurately align the solution to your requirements.
Thank you.
Super UserVisual calculation
Add this as visual calc on your chart (Y-axis max field):
Y Max =
VAR CurrentLevel = IF(ISINSCOPE('Date'[YearWeek]), "Week", "Month")
VAR MaxSales =
SWITCH(CurrentLevel,
"Week", MAXX(ROWS, [Sales]),
MAXX(ROWS, [Sales])
)
VAR MaxPlan =
SWITCH(CurrentLevel,
"Week", MAXX(ROWS, [Sales Plan]),
MAXX(ROWS, [Sales Plan])
)
RETURN MAX(MaxSales, MaxPlan) * 1.1
Set chart Y-axis Max = this visual calc. Adapts to drill state automatically.
Hi, This evaluate Y Max as the max of total sales/total plans. So its do that I'd like.
Hi somogyitamas88 ,
You’re right about the issue. The chart is using total values instead of the highest value among the visible bars, so when you drill down to weeks, the Y-axis is still based on larger totals and doesn’t scale correctly.
In the shared .pbix, I fixed this by making the Y-axis look at the maximum value across the currently visible Month/Week level, comparing Sales and Sales Plan for each point. This way, the axis adjusts properly, showing the correct max at month level and switching to the correct max at week level when you drill down.
Thank you.Unfortunately this is not a proper solution. Y max value is 330 whether we are on month or week level, which is the 1.1 times value of max weekly data.
