Set Analysis with Axes Min/Max, Should Not change on Filter

[a272070]

Hello,

I am using a set analysis expression in my chart.

Sum({1<[Calendar Year] = {"$(=Max({1}[Calendar Year]))"}>} DISTINCT [Sales Percentage]) <-- Used for current year

Sum({1<[Calendar Year] = {"$(=Max({1}[Calendar Year])-1)"} >} DISTINCT [Sales Percentage]) <--- Used for previous year

The dimension for this chart is [Calendar Month]. 

Here is the chart below that it makes whenever I put the min and max values in the axes tab. I put min(Sales percentage) and max(Sales percentage) for both the current year and previous year expressions in the scale section under the axes tab. 

If I don't put the min and max values, then the chart looks like this, which is not as nice and spaced out. 

The issue is, I don't want this chart to do anything when I filter, I want it to stay the same as the set analysis should with the {1} where they are currently as shown above...

But the issue is, when I filter and I have the min and max values in the axes tab, it does this: 

Is there a way to fix this so it does not change when filtering but I can also use the min and max axes values so that it looks like the second image at all times no matter what filter?

Thanks in advance!

[stevejoyce · Accepted solution]

First you need to carry your set analysis to your min/max axis calculation.  I am doing >= max year -1 which will include both years.

min({1<[Calendar Year] = {">=$(=Max({1}[Calendar Year])-1)"}>} [Sales percentage])

max({1<[Calendar Year] = {">=$(=Max({1}[Calendar Year])-1)"}>} [Sales percentage])

But this (and your original calculation) aren't aggregating Sales percentage by month for min/max... this is just the max row value.  That would only be the same if you have 1 value per month.

If you do need to aggregate first it would be like this:

=max({<[Delivery date] = {">=$(=Max({1}[Calendar Year])-1)"}>}aggr(sum({<[Delivery date] = {">=$(=Max({1}[Calendar Year])-1)"}>} [Sales percentage]), [Calendar Month]))

and change outermost function to min.

[a272070]

Thank you so much. This worked for me!