Measure to find a Score within a Range

[bassmaninaus_PB]

Hi, need help with a measure.

I'm trying to calculate the Score based on daily Variances.

The Scores Table looks like this..

Forecast Nos	Variance Min	Variance Max	Score
1	0	0.05	5
1	0.05	0.1	4
1	0.1	0.2	3
1	0.2	0.3	2
2	0	0.05	5
2	0.05	0.1	4
2	0.1	0.2	3
2	0.2	0.3	2

The table goes for 3000 or so rows and scores eventually change depending on the Forecast Nos getting larger. A Score of 1 to be applied if variance is out of the above ranges.

The measure will need to look at (filter) for the forecast call numbers to get the ranges 

Forecast Nos = SUM(Actuals_SQL_ResMgmt[Res Forecast Queued (Net)])

Also, if the Forecast Nos is greater than the number of rows in the above table so the last Forecast Nos variance ranges and scores are to be applied to that.

The Variance measure to get the Score from the Score Table is the following.. 

Results RM Vol % Var Absolute = AVERAGEX(VALUES(Actuals_SQL_ResMgmt[Date]),ABS(Measures_Actual_Res[Results RM Vol % Var]))

Thanks in advance.

[bassmaninaus_PB]

Thanks for the info, next time I'll try to get sample data. 

[v-rongtiep-msft · Accepted solution]

Hi @bassmaninaus_PB ,

What is your desired output?

Please provide more details with your desired output and pbix file without privacy information (or some sample data) .

How to Get Your Question Answered Quickly 

Best Regards
Community Support Team _ Polly

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.