Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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! Go to 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.
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.
Thanks. This helped me solve a similar problem !
@jonasr,
Glad to hear the issue is solved, you can accept your reply to close this thread.
Regards,
Lydia
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
113 | |
99 | |
75 | |
73 | |
49 |
User | Count |
---|---|
146 | |
108 | |
106 | |
90 | |
62 |