The knowledge base article does help get the right answer, but only at certain levels of aggregation. In your example, the technique will get the right answer per case, but when you try to get an average of all cases, you get the wrong answer, as you noted. That's because it is an average of averages at that point.
So, instead, try something like this:
1. Start with a view of the data at an aggregation that gives you the right answer per case (I chose MIN, but MAX or AVG would work too.) This is what the KB article gives -- but you'll need a couple more steps to get the overall average.
2. Create a table calculation. I named mine [Average].
WINDOW_SUM(MIN([total time taken])) / SIZE()
The table calculation is calculated Table Down
The calculation adds up the minimum time (for each case_number, as that field on Rows is determining the level of detail). The SIZE() calculation gives the size of the window. As the calculation is performed Table Down, the size of the window is the same as the number of cases.
The calculation gives the same result for each row, so it really only needs to be calculated for the first row. For performance, it could be written: IF FIRST() == 0 THEN WINDOW_SUM(MIN([total time taken])) / SIZE() END
3. Clean up the view to show the answer without showing it for each case:
The [First Filter] calculation is simply: First() == 0. I kept the true value, so only the first row of the table in the final view is shown. I removed fields I didn't need and I also hid the row headers for case_number (right click the field on Rows and uncheck "Show Header").
I've attached the workbook so you can see each step. Hopefully that helps!
Duplicated Data.twbx 20.3 KB
This is outstanding, Joshua! Thank you so much.
You're welcome! Glad to help!