Tableau: exclude current row data from window average

Question

I have a student average marks information and I am working on performing some statistical things to find out the significance of the student vs overall score. Below is the screenshot of my data and I have also attached the data for reference.

I am able to get the overall window average using the tableau's WINDOW_AVG function however I want to exclude the current row from being considered in the average.

For example, when I am calculating the overall average of student 7210, I must leave the 7210 student's Avg (83.320754717) and use the remaining student average for the overall average calculation. Similarly, when calculating for 7211, I must leave the student avg (77.75806452) from being calculated in the overall.

I have attached the data for reference in this link.

Can anyone let me know how to proceed with this calculation?

Answer 1

• Below mentioned  is the formula for computing the class average excepting the current student, written as a table calc.

(zn(window_sum(avg([Student's Avg]), first(), -1)) + zn(window_sum(avg([Student's Avg]), 1, last()))) / (size() - 1)

Or equivalently,

(window_sum(avg([Student's Avg])) - lookup(avg([Student's Avg]), 0)) / (size() - 1)

• You would need to specify the partitioning and addressing. In this simple case, you can just set "compute using" to StudentId.

• Table calcs are computed by Tableau acting upon the table of aggregated query results returned by the data source - hence the aggregation function avg() around the field [Student's Avg]. If there is only value per student, then the choice of SUM(), AVG(), MIN() etc doesn't matter. If your data source has more than one data row per student, say for each test score, then you would want to be sure to use AVG() in this case.