I have a data source that contains evaluation forms where each individual evaluation (with unique ID) has a total score. Each evaluation also has different numbers of subsections and so the unique evaluation ID keeps repeating along with the total score with each subsection.
I am trying to figure out how to correctly calculate average score for all evaluations. Please see attached file as an example.
In the example I have four forms with scores 2.67, 2.2, 2.89 and 2.50. The correct average score should be (2.67+2.2+2.89+2.5) / 4 = 2.565. Two of the forms have 103 subsections and two forms have 95 subsections. Because of this Tableau takes sum of all scores (1013.66) and divides by number of rows (396) = 2.559747 on Sheet1 which is not correct.
How can I calculate the average for all forms to somehow only take one "Total Score" value for each unique "ID"?