I have a table in which I have a column listing all systems and the corresponding score arising from the expression noted above. I have also created another dimension in which if the score = 100, 'Yes', if score < 100, it returns 'No'.
However, i want to have a table which shows the number of '100 scored' systems within each unit. the problem when i replace system with unit in the table is that it takes a single figure average of all the system scores within that unit. so if 10 systems had a score of 100 and 10 had a score of 50 it would give a singular score of 75 for that unit. Is there a way in which i can count the number of systems in that unit that have a score of 100 using the aggr function?
The problem is....when I try to do this, the average score of the whole unit is applied. So this may be 95% and therefore a 0 will be applied to the whole unit. What I require is, the score of each system to be taken into account within a table only displaying the unit.
Just to make it clearer, can create a table like this:
Unit
System
Score
if(Score = 100, 1,0 )
A
1
100
1
A
2
100
1
A
3
98
0
A
4
98
0
A
5
100
1
If I remove System as a dimension and I add a sum(if(Score = 100, 1,0)), it takes an average of the scores i.e. (100+100+98+98+100)/5 and therefore returns 0 as a value where I would like it to be:
