Currently i have this table
As you can see, my dimensions are Questions and Rotation.
The set analysis below is my expression.
=Avg({<[Rotation],Questions={'Q1','Q2','Q3','Q4','Q5','Q6','Q7','Q8','Q9','Q10','Q11','Q12','Q13','Q14','Q15','Q16','Q17','Q18','Q19','Q20','Q21','Q22','Q23','Q24','Q25','Q26'}>}Aggr(((SUM(Data)/
COUNT({<Data-={"NA"}>}Rotation)))
, Questions,Rotation))
By checking the box to show partial sums for both dimensions, i will get "Rotation on Avg Score" and the last row, as the Program Average.
Unfortunately, the result is not exactly how i want it. NA represents empty and this calculation takes an average of all including NA.
For example, Q9's program average should be 5.13 and not 1.28.
I understand that the problem lies with the set analysis where i declare Questions={'Q1',........,'Q26'} .
I was looking into dynamic set analysis which could work but need some guidance.
