Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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
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?
Solved! Go to 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
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
Sorry - posted too quickly. Tried again, and it worked. Must not have done it right the first time. Thanks!