Using average of between slicer to calculate a measure

[mrothschild]

I have a slicer associated with interest rate integers, i.e., 

4%

5%

6%

7%. . etc.

I have a measure that uses the interest rate as a function.  I want my users to be able to insert the range (using a between slicer) and use that as the input into the measure calculation.

So if a user enters 5.3% into the bottom and top end of the slicer, I want the measure to return 5.3%.  

If a sure enders 4% and 5.5% into the top and bottom end of the slicer, I want the measure to return (4% + 5.5%)/2 = 4.75%

I've tried:

VAR max_i_rate = MAXX(ALLSELECTED('IRR Sensitivity - Levered'[Interest Rate (rounded)]),'IRR Sensitivity - Levered'[Interest Rate (rounded)])

VAR min_i_rate = MINX(ALLSELECTED('IRR Sensitivity - Levered'[Interest Rate (rounded)]),'IRR Sensitivity - Levered'[Interest Rate (rounded)])

VAR i_rate = (max_i_rate + min_i_rate) / 2

RETURN

i_rate

but the output is "(Blank)" when entering the same value for the bottom and the top.

when I enter 4% and 5.5% the output is 4.5% because, I assume, it's rounding the 5.5% to 5% and then returning (4% + 5.0%)/2

What do I need to change about the Measure calculation programming to achieve desired result?

Thanks!

[amitchandak]

@mrothschild , Make sure Levered'[Interest Rate (rounded)] is a decimal number. Also what values you are entering, should be available in the column Levered'[Interest Rate (rounded)]

[mrothschild]

Thanks @amitchandak 

Is it possible to use a selection that's not available in that column?  

For example, if my slicer is set to one decimal place, so the slider moves from 4.0% --> 4.1% --> 4.2% --> 4.3%, etc., but the column data only consists of 4 numbers: 4%, 5%, 6%, 7%, is there a way for me to output (for further calculations) 4.1%, 4.15% [=average of 4.1% and 4.2%], or 4.2%.

Or do I need to re-work the underlying data to include all those "empty" rows of data?

[amitchandak]

@mrothschild , the allselected on the column will force the value from that column only.

You can create a series using generateseries having all values

[mrothschild]

@amitchandak 

PBIX Link: https://drive.google.com/open?id=1KXxee9BCMN68eKWgL0PgUEqTlHgklvJm

Here's the issue I'm running into trying your suggestion:

I have a Matrix that has data populated, which I'd like to constrain with my Slicers (separate from the aforementioned Measure).  When I generate a Parameters table using GENERATESERIES and create a relationship between that table and the underlying Interest Rate column, the Measure works as intended now, but the Matrix no longer works correctly.  

For example, here is an abridged screenshot with nothing constrained:

Here is a screenshot with the upper end of Interest Rate column sliced:

In both of the above, the Matrix and the Measure are outputting as I hoped.  The Measure is calculating the average of the range in the Between Slicer, and the Matrix is showing the output values for a 4.0% interest rate.

In the following, I've sliced the lower end of the between to 3.9% interest rate.  The 4% interest rate on the Matrix should not be affected by this because it's still within the range of the Slicer.  But all of the data in the Matrix disappear.