Forum Discussion

SB2653's avatar
SB2653
New Member
3 years ago
Solved

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   
DateTeamNameTimeMinutes
01/01/2023Dept. AJustin1
02/01/2023Dept. APeter1
03/01/2023Dept. BSteve2
04/01/2023Dept. CMary1
05/01/2023Dept. DSarah2
06/01/2023Dept. BJanet3
07/01/2023Dept. BSteve3
08/01/2023Dept. DPaul2
09/01/2023Dept. CMary1
10/01/2023Dept. CStan2
11/01/2023Dept. AJustin1
12/01/2023Dept. BJanet

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
        _result
    

     

    Below 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

  • 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
        _result
    

     

    Below target: = 
    VAR _tablerowcount =
        CALCULATE ( COUNTROWS ( Table1 ), REMOVEFILTERS () )
    VAR _target =
        DIVIDE ( CALCULATE ( [Minutes:], REMOVEFILTERS () ), _tablerowcount )
    VAR _result =
        COUNTROWS ( FILTER ( Table1, [Minutes:] < _target ) )
    RETURN
        _result