Forum Discussion
Dynamic Y range visual calculation in two level hierarchy
Hi!
I have a column chart.
To the rows I put two level of dates: YearMonth and YearWeek
To the values I put Sales, Sales plan.
I would like to make a visual calculation solution for the Y axis Max range value which is considered wheter we are on month or week level. Of course when we are on month level in hierarchy, I would like to reach the common max value from monthyl sales & monthly sales plan values. When we drill down to the week level in the visual (or month-week level), I would like to get the common max value from weekly sales & weekly sales plan values.
Do you have good solution?
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.
20 Replies
Visual 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.
One way to handle this is to use ISINSCOPE to check whether the visual is at the YearMonth or YearWeek level. From there, you can calculate the highest value between Sales and Sales Plan at that level. This way the Y-axis maximum adjusts automatically when you drill in the chart.
Please can you show images of visuals where you show what is the situation now and what you want to achieve, showing notes around the visuals so we can understand
If this helped, please consider giving kudos and mark as a solution
@me in replies or I'll lose your thread
Want to check your DAX skills? Answer my biweekly DAX challenges on the kubisco Linkedin page
Consider voting this Power BI idea
Francesco Bergamaschi
MBA, M.Eng, M.Econ, Professor of BI
I know this solution 🙂 I would like to try a simpler one with Vis Calc. 🙂
Hi somogyitamas88 ,
Thank you Kedar_Pande for the response provided!
Has your issue been resolved? If the response provided by the community member addressed your query, could you please confirm? It helps us ensure that the solutions provided are effective and beneficial for everyone.
Thank you.
