using avg in set analysis

[frederikverhaeghe]

Hello,

I have a table containing training scores of employees, in a range of 0 to 4. 

I would like to count the number of employees scoring an average higher than 3. 

Sounds ridiculously simple, but I do not seem to make it work. 

I tried count( { $ < avg(score) = {">=3"} > distinct name }, but that does not work. 

I am using enterprise, with no access to the load script...

Hoping for your inspiration!

[SerhanKaraer]

Hello Frederik,

set expression must have a filter on a field, you can not use expressions to filter.

I think the expression below solves your problem.

count({<Employee={"=avg(Score)>=3"}>} DISTINCT Employee)

[frederikverhaeghe]

Hello Serhan,

unfortunately I get an empty list now...

[agigliotti]

Hello,
@SerhanKaraer  's answer should works for you.
please check if you are using the right field names in the expression.

[frederikverhaeghe]

hello,

you are completely right, there was a typo in my formula. 

Now it works. 

There is one complication, however. 

The training tasks are grouped in competence profiles, each consisting of a number of tasks. 
The employees are grouped in teams. 

The pivot table has the teams in the rows, the profiles in the columns, and should have the number of employees in a team, scoring on average above 3 on all tasks within the profile, in the cells. 

Now it seems the formula you have provided, does not take into account the columns...

[agigliotti]

currently which are your pivot table dimensions and measures?
what is the actual result and the expected one?

[frederikverhaeghe]

Hello,

here is a simplified example:

in the table right bottom I would expect 1 and 2 instead of 3 and 3 ...

[sunny_talwar · Accepted solution]

You can try this

Sum(Aggr(
    If(Avg(Score) >= 3, 1, 0)
, name, profile))

[frederikverhaeghe]

yay!