Count number of values within a range of the average of those values

[bpmccain]

Hi,

I have some data similar to what is shown below:

Date	Sample	Value
March 20th, 2024	1	5
March 20th, 2024	2	6
March 20th, 2024	3	7
March 20th, 2024	4	3
March 20th, 2024	5	4

The average of these values is 5, and I want a measure that I can use that shows me the count of values that are +/-1 of that average. The matrix table I would like to show at the end should look like this:

Date - Sample	Average of Value	Count within 1 of average
March 20th, 2024	3	3
1	5	1
2	6	1
3	7	0
4	3	0
5	4	1

The expression I am using as a measure is:

In Range = 

var _average = Calculate(AVERAGE('Data'[VALUE]),REMOVEFILTERS('Data'[Sample]))

var _delta = abs(average('Data'[VALUE])-_average)

var _inRange = CALCULATE(COUNTA('Data'[VALUE]),FILTER('Data',_delta<=1))

return _inRange

What I end up with however is, where the total count is wrong

Date - Sample	Value	Count within 1 of average
March 20th, 2024	3	5
1	5	1
2	6	1
3	7	0
4	3	0
5	4	1

Any ideas?

[wdx223_Daniel · Accepted solution]

is this ok?

In Range = 

var _average = Calculate(AVERAGE('Data'[VALUE]),REMOVEFILTERS('Data'[Sample]))

var _inRange = CALCULATE(COUNTA('Data'[VALUE]),FILTER('Data',abs('Data'[VALUE]-_average)<=1))

return _inRange

[bpmccain]

Sorry - posted too quickly. Tried again, and it worked. Must not have done it right the first time. Thanks!