Forum Discussion

jonasr's avatar
jonasr
Advocate I
8 years ago
Solved

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

  • 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

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

    • Anonymous's avatar
      Anonymous
      Not applicable

      Thanks. This helped me solve a similar problem !

    • Anonymous's avatar
      Anonymous
      Not applicable

      jonasr,

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

      Regards,
      Lydia