Expression (Average) that calculates based on selected/filtered value as well as other peer groups which the selection belongs to

[sbfernandes]

Greetings. 

I have the below data:

Name	Gender	Class	Subject	Grade
John	M	A1	Math	5
John	M	A1	Science	4
John	M	A1	French	3
John	M	A1	Misc	2
Jane	F	A1	Math	4
Jane	F	A1	Science	5
Jane	F	A1	French	3
Jane	F	A1	Misc	3
Joe	M	A2	Math	4
Joe	M	A2	Science	1
Joe	M	A2	French	4
Joe	M	A2	Misc	2
Janice	F	A2	Math	4
Janice	F	A2	Science	3
Janice	F	A2	French	1
Janice	F	A2	Misc	1

I would like to create a Table chart that will display the Average Grade by Person Name. In addition, for that person it will also display the average Grade by gender as well as Class.

Output chart with Average values is as per below:

	Average Grade	Overall average grade by gender	Overall average grade by Class
John	3.5	3.125	3.625
Jane	3.75	3.000	3.625
Joe	2.75	3.125	2.500
Janice	2.25	3.000	2.500

Eg: Overall average grade by gender will consider John's grade as well as all grades that match his gender. So Overall average grade by gender for John = Average(5, 4, 3, 2, 4, 1, 4, 2) = 3.125

Further if user selects a name say John, the output should be as per below:

	Average Grade	Overall average grade by gender	Overall average grade by Class
John	3.5	3.125	3.625

Thus far, i have not been able to get the correct expression for the last 2 expressions.

Thank you for your time.

[edwin · Accepted solution]

to get the first table with the 3 expressions, you must be agregating your data to come up with the last 2 expressions with something like this:

aggr(nodistinct Avg(Grade),Gender)
aggr(nodistinct Avg(Grade),Class)

to keep the correct averages by class and gender regardless of what name is selected, add set analysis ignoring the selection:

aggr(nodistinct Avg({<Name=>}Grade),Gender)
aggr(nodistinct Avg({<Name=>}Grade),Class)

[sbfernandes]

Many thanks for this.

[sbfernandes]

If i may add an additional complexity,

if the table is as per below where John now belongs to two different Class. When using the formula, i get a null value. i assume this is due grouping by 2 Class "aggr(nodistinct Avg({<Name=>}Grade),Class)"

Name	Gender	Class	Subject	Grade
John	M	A1	Math	5
John	M	A1	Science	4
John	M	A1	French	3
John	M	A1	Misc	2
Jane	F	A1	Math	4
Jane	F	A1	Science	5
Jane	F	A1	French	3
Jane	F	A1	Misc	3
Joe	M	A2	Math	4
Joe	M	A2	Science	1
Joe	M	A2	French	4
Joe	M	A2	Misc	2
Janice	F	A2	Math	4
Janice	F	A2	Science	3
Janice	F	A2	French	1
Janice	F	A2	Misc	1
John	M	A3	Math	4
John	M	A3	Science	4
John	M	A3	French	3
John	M	A3	Misc	5

I i would like to get a value based on both class that John belongs to, is it possible? Overall average grade by Class for John would be : Average(5, 4, 3, 2, 4, 5, 3, 3, 4, 4, 4, 5) = 3.75.

This average includes scores for John (both classes A1 and A3) as well as other scores for class A1 and A3.