cancel
Showing results for
Did you mean:
Frequent Visitor

## DAX Measure calculating COUNT based on condition over calculated average value

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

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Frequent Visitor

## Re: DAX Measure calculating COUNT based on condition over calculated average value

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.

2 REPLIES 2
Highlighted
Frequent Visitor

## Re: DAX Measure calculating COUNT based on condition over calculated average value

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. v-yuezhe-msft
Moderator

## Re: DAX Measure calculating COUNT based on condition over calculated average value

@jonasr,

Regards,
Lydia

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Announcements #### New Ranks Launched March 24th!

The time has come: We are finally able to share more details on the brand-new ranks coming to the Power BI Community! #### ‘Better Together’ Contest Finalists Announced!

Congrats to the finalists of our ‘Better Together’-themed T-shirt design contest! Click for the top entries. #### Arun 'Triple A' Event Video, Q&A, and Slides

Missed the Arun 'Triple A' event or want to revisit it? We've got you covered! Check out the video, Q&A, and slides now. Top Solution Authors
Top Kudoed Authors
Users online (3,703)