Forum Discussion
Count of values above & below the average
I have a table of data and I'm looking to create a visual that will show a distinct count of names of people who spend above & below the average amount of time on something - Literally 2 columns - X number of people above average, Y number of people below average
I've written a number of measures to get me to a point but my end result keeps giving me a distinct count of total records rather than a filtered value.
Any help & guidance would be much appreciated.
Below is a sample data set, along with the measures i've written so far
Sample data:
| Table1 | |||
| Date | Team | Name | TimeMinutes |
| 01/01/2023 | Dept. A | Justin | 1 |
| 02/01/2023 | Dept. A | Peter | 1 |
| 03/01/2023 | Dept. B | Steve | 2 |
| 04/01/2023 | Dept. C | Mary | 1 |
| 05/01/2023 | Dept. D | Sarah | 2 |
| 06/01/2023 | Dept. B | Janet | 3 |
| 07/01/2023 | Dept. B | Steve | 3 |
| 08/01/2023 | Dept. D | Paul | 2 |
| 09/01/2023 | Dept. C | Mary | 1 |
| 10/01/2023 | Dept. C | Stan | 2 |
| 11/01/2023 | Dept. A | Justin | 1 |
| 12/01/2023 | Dept. B | Janet | 1 |
Measure 1
Average total time spent per day (minutes) = [m. Sum of TimeMinutes]/12
Measure 2
Number of People = DISTINCTCOUNT('Table1'[Name])
Measure 3
Average time spent per person per day (Minutes) = [Average total time spent per day (minutes)]/[Number of People]
Final measure to give me number of people below the average time value that I can't get to work:
Count of People Below Average =
CALCULATE(
DISTINCTCOUNT('Table1'[Name]),
FILTER('Table1',
[Average time spent per person per day (Minutes)] < 1.6
))
Thanks
Hi,
I am not sure if I understood your question correctly, but please check the below picture and the attached pbix file.
Above target: = VAR _tablerowcount = CALCULATE ( COUNTROWS ( Table1 ), REMOVEFILTERS () ) VAR _target = DIVIDE ( CALCULATE ( [Minutes:], REMOVEFILTERS () ), _tablerowcount ) VAR _result = COUNTROWS ( FILTER ( Table1, [Minutes:] > _target ) ) RETURN _resultBelow target: = VAR _tablerowcount = CALCULATE ( COUNTROWS ( Table1 ), REMOVEFILTERS () ) VAR _target = DIVIDE ( CALCULATE ( [Minutes:], REMOVEFILTERS () ), _tablerowcount ) VAR _result = COUNTROWS ( FILTER ( Table1, [Minutes:] < _target ) ) RETURN _result
2 Replies
- Jihwan_Kim
Super User
Hi,
I am not sure if I understood your question correctly, but please check the below picture and the attached pbix file.
Above target: = VAR _tablerowcount = CALCULATE ( COUNTROWS ( Table1 ), REMOVEFILTERS () ) VAR _target = DIVIDE ( CALCULATE ( [Minutes:], REMOVEFILTERS () ), _tablerowcount ) VAR _result = COUNTROWS ( FILTER ( Table1, [Minutes:] > _target ) ) RETURN _resultBelow target: = VAR _tablerowcount = CALCULATE ( COUNTROWS ( Table1 ), REMOVEFILTERS () ) VAR _target = DIVIDE ( CALCULATE ( [Minutes:], REMOVEFILTERS () ), _tablerowcount ) VAR _result = COUNTROWS ( FILTER ( Table1, [Minutes:] < _target ) ) RETURN _result- SB2653New Member
Thanks Jihwan_Kim for taking the time to look at this, much appreciated 👍