cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

## 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

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.

3 REPLIES 3

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.

New Member

Thanks. This helped me solve a similar problem !

Microsoft Employee

@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

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.