3 of 3 people found this helpful
The Grand Total in your workbook is really calculating the AVG([Total Score]). This is happening due to the "ATTR" function in your "Final Score" calculation. ATTR returns a value only when all values in the data set are the same. Since the 'type' is different throughout the data set, this is causing the 'else' condition in the "Final Score" calculation to be triggered when calculating the Grand Total.
To make the Grand Total work as you want you need to build the calculations a bit differently. Attached is an example, and I'll attempt to describe how it's working.
You'll need three intermediate calculations that can be used in the "Final Score (reworked calculation)". This is necessary since you want to treat some results as SUM and some results as AVG in the final score. With the three calcs built out you can then create a new calc to do what you're looking for, which is to aggregate the sets in different ways.
The "Final Score (Avg_1)" calc returns a value only if the 'type' is 'Avg_1':
IF [Type] = "Avg_1" THEN [Score] ELSE Null END
The "Final Score (Avg_2)" calc returns a value only if the 'type' is 'Avg_2':
IF [Type] = "Avg_2" THEN [Score] ELSE Null END
The "Final Score (Independent)" returns a value only if the 'type' is 'Independent':
IF [Type] = "Independent" THEN [Score] ELSE Null END
The "Final Score (reworked calculation)" can now be used to sum up the results and aggregate each set of values independently. The ZN() function is used to handle the NULL values.
ZN(Avg([Final Score (Avg_1)])) + ZN(Avg([Final Score (Avg_2)])) + ZN(Sum([Final Score (Independent)]))
This approach result in the table below, which I believe is what you were looking for.
Not the most straightforward solution, but it works.
Worked great. Thanks!