sum(avgOver(...)) Nested aggregation not allowed

Question & Answer
1

Hi QuickSight Community,

My dataset:

Student Course Questions Answered
A X 123 67
A Y 222 55
B Z 999 234
B W 808 789
C X 123 0
D W 808 808
E Y 222 101

avgOver(avg(Questions), [Course]) →

Course Questions Participants
X 123 2
Y 222 2
Z 999 1
W 808 2

Total: 123+222+999+808
Is there any way to get the total number of questions in all courses if sum(avgOver(…)) is not allowed?

Having a dataset that doesn’t include participants is not an options because some filters/controls are in place. E.g. filtering by students from EMEA would change the total number of questions from 123+222+999+808 to 123+808 because there are no students from EMEA in course Y and Z.

I hope it makes sense. I’d appreciate any kind of help!

Thanks,
Adrian

2

You’re looking for the total number of questions in all courses?

Can it be

sum(avgOver(Questions, [Course],PRE_AGG))

3

Tried that and it simply gives me a number equal to sum(Questions) in the initial dataset. Tried with PRE_FILTER as well, but same result.

I’m using this value in a KPI visual, fortunately I have another dataset that has a question level granularity, e.g.:

Student Course Question Score
A X Q 10

So I can have a calculated field that would give me unique IDs fro each question in a course:

concat(toString({course_id}), '_', toString({question_id}))
Student Course Question Score Course_Question
A X Q 10 X_Q

Then I can display this field in a KPI like Course_Question(Count distinct).