Hi,
I am collating the data i have collected into a bar chart whereby i would need to create 3 groups to compare the difference per year. The data i collected are similar to how it is below:
| Department | Q1 | Q2 | Q3 |
| Dept 1 | 10 | 10 | 5 |
| Dept 2 | 10 | 5 | 5 |
| Dept 3 | 10 | 5 | 5 |
| Dept 4 | 10 | NA | 5 |
| Average | 10 | 5 | 5 |
Each group consists of tables of data whereby some data could be stated as NA. Where the data is NA the final average should be calculated excluding the average, for example, for Q2, the average should be 6.66 and not 5 as the NA value should be excluded and the total sum should be divided by 3 instead.
So currently,since i have 3 group, how I'm deriving to this value is by AVG(AGGR(AVG({[Group 3]}Data),Faculty, Questions,Year))
As i want to exclude data which is NA, i need to include something like Data-='NA', but how do i do it as this is the data from a group. in this scenario, the group name is Group 3.
Please help. Thanks.
