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?

Read the reference section of the manual about window_sum(), first(), last(), Index() and size() — Alex Blakemore
@AlexBlakemore. The manual has all details regarding only the functions and it does not provide information on how to exclude value. the first() allows you to pick the first value, last() will point to the last value, window_sum() is used to sum the overall values. No where its mentioned in calculating values by excluding the rows which itis getting calculated. — Karthik Venkatraman
Try writing a formula that sums the range up to, but not including, the current item, and add that to the sum of the values after the current item, then divide by one less the number of items. — Alex Blakemore
@AlexBlakemore I can do this easily in excel by the same way you are saying. But since I am using tableau to produce the end view, i am not able to find the correct function to use. I thought of using filters but it will not work as the student id is dynamic. — Karthik Venkatraman

Alex Blakemore Alex Blakemore · Accepted Answer · 2017-03-15T00:01:09

Here 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)

Like any table calc, the formula is only part of the story. You'll also have 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.

Tableau: exclude current row data from window average

1 Answers