Aggregation of LAC function

Question & Answer
1

Hi, I have a table that looks like below.

school grade class student_id
X 6 Grade English 01
X 6 Grade Math 01
X 6 Grade Math 02
X 7 Grade English 03
X 7 Grade English 04
X 7 Grade Sports 03

I would like to calculate percentage of students per class among students per grade. The output table should look like:

school grade class students/class students/grade percentage
X 6 Grade English 1 2 50%
X 6 Grade Math 2 2 100%
X 7 Grade English 2 2 100%
X 7 Grade Sports 1 2 50%

I have created a chart of ‘Table’ at school/grade/class level, with two calculated fields. One is students per class, i.e. distinct_count({student_id}, [{school}, {grade}, {class}]) . The other one is students per grade, i.e. distinctCountOver( {student_id}, [{school}, {grade}], PRE_AGG).

Is it able to calculate the percentage column?

2

You can wrap it in a percentOfTotal and partition by grade.

3

Hi @Tina
Did Max’s solution work for you? I am marking his reply as, “Solution,” but let us know if this is not resolved. Thanks for posting your questions on the QuickSight Community Q&A Forum!

4

Thank you for the help and sorry for the late response, the idea works! I want to add a note. My case is actually a bit more sophisticated than my example above, which contains duplicate records and percentOfTotal may not be accurate. My solution is to change numerator to distinctCountOver({student_id}, [{school}, {grade}, {class}], PRE_AGG), then create another calculated field as numerator/denominator.