topic Count number of values within a range of the average of those values in DAX Commands and Tips

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

bpmccain — Thu, 21 Mar 2024 01:10:37 GMT

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?

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

wdx223_Daniel — Thu, 21 Mar 2024 04:10:44 GMT

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

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

bpmccain — Thu, 21 Mar 2024 14:11:41 GMT

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