Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric 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!
User | Count |
---|---|
62 | |
46 | |
19 | |
18 | |
15 |
User | Count |
---|---|
119 | |
41 | |
40 | |
28 | |
22 |