DAX Measure calculating COUNT based on condition over calculated average value

[jonasr]

Hi, I am struggling to find the right way to calculate the count of people that have average value of some variable above certain treshold and show this number in a Card visual.

Basically, I have data similar to this:

Employee ID | Period  | Score
1           | 201801 | 0.5
1           | 201802 | 0.8
1           | 201803 | 0.7
1           | 201804 | 0.9
1           | 201805 | 0.6
2           | 201801 | 0.9
2           | 201802 | 0.8
2           | 201803 | 0.8
3           | 201801 | 0.5
3           | 201802 | 0.4
3           | 201803 | 0.3
3           | 201804 | 0.5

I need to calculate average score for each employee:

Employee ID | Average Score
1           | 0.7
2           | 0.83
3           | 0.43

And then calculate the number of employees that have score below treshold 0.8 and show this summary statistic in a Card Visual (the count should be 2 in the example above).

All this needs to be done as a Measure since the selection of rows in the first table can be influenced by various filters/slicers.

What I have tried is combination of two measures:

Average Score = CALCULATE(AVERAGE('table'[Score]),ALLEXCEPT('table','table'[employee_id]))

Average Score Count = 
CALCULATE (
    DISTINCTCOUNT ( 'table'[employee_id] ),
    FILTER ( 'table', [Average Score] < 0.8 )
)

The second measure is what I put in the Card Visual.

However, I am not getting the right count, so I am guessing that my DAX approach is not correct.

Any ideas?

Thanks

[jonasr · Accepted solution]

Solved it. This worked:

ScoreBelow0.8 = 
COUNTROWS(
    FILTER(
        SUMMARIZE (
        'table',
        'table'[employee_id],
        "SCORE", 
            AVERAGE('report sector_member'[Score])
         ),
    [SCORE] < 0.8 && ISBLANK([SCORE]) = FALSE
    ) 
)

The major issue was handling of BLANK values in Score column in subsequent filtering.

[tonyB291]

Thanks. This helped me solve a similar problem !

[v-yuezhe-msft]

@jonasr,

Glad to hear the issue is solved, you can accept your reply to close this thread.

Regards,
Lydia