Explore Courses Blog Tutorials Interview Questions
0 votes
in BI by (47.2k points)

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.

Sample Data

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?

1 Answer

0 votes
by (17.6k points)
  • 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.

Related questions

0 votes
1 answer
asked Jul 18, 2019 in BI by Vaibhav Ameta (17.6k points)
0 votes
1 answer
0 votes
1 answer
asked Oct 10, 2020 in BI by dev_sk2311 (45k points)

Browse Categories