2 Replies Latest reply on Feb 28, 2017 6:45 AM by Brandon Shelton

    SUM total for calc'd field that combines AVG and SUM

    Brandon Shelton

      Attached is a very simple workbook example. I created the 'Final Score' calculation to SUM 'Score' when the 'Type' = "Independent" and AVG 'Score' for all other instances. I am trying to simply calculate the sum of all Types using this logic which would be 11.5.

       

      'Avg_1' = 2

      'Avg_2 = 2.5

      'Independent' = 7

       

      How do I display 11.5 by itself?

        • 1. Re: SUM total for calc'd field that combines AVG and SUM
          Ryan Eavey

          Hi, Brandon,

           

          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.

          3 of 3 people found this helpful