2 Replies Latest reply on Jul 18, 2018 9:38 AM by Diane Royer

    Calculation Won't Summarize

    Diane Royer

      I am trying to put a worksheet together that contains a hierarchy.  The hierarchy contains the college, department, and course (thus tying the course to the department and college it belongs too) for each class offered during a school year.  I have this calculation ([STUDENT_NET_CONT_PER_CR_HR])*ATTR([Credit Hrs]) to calculate the revenue for the course.  The calculation works great at the course level.  However, the dollar amounts disappear when I drill up to the school level.  Is there a way to get the calculation to work for the school level and the course level?  Also, when I add this calculation to the workbook, it doesn't sum, it defaults to "Agg" and I am not given the option to change it to sum.

       

      Any assistance would be greatly appreciated.

        • 1. Re: Calculation Won't Summarize
          Ryan Ledoux

          Hi Diane,

           

          ATTR() only returns a result when there is a single value, and since the number of credit hours aren't the same for each course, that's why this doesn't work.

           

          I would use something like the following, which multiplies the credit hours for each course by the number of students, and then sums across courses. You may need to edit the calculation depending on your filters:

          SUM({ Fixed [course]: Max([Credit Hrs])}*[STUDENT_NET_CONT_PER_CR_HR])

          1 of 1 people found this helpful
          • 2. Re: Calculation Won't Summarize
            Diane Royer

            Hello,

             

            Thank you very much for the advice.  I have another question for you.  This is the calculation I have now: (sum({ Fixed [FISCAL_YR],[School Cde],[Institut Div Cde]: sum([Credit Hrs])}/{ Fixed [FISCAL_YR],[School Cde],[Institut Div Cde]: COUNT([Id Num])})*([STUDENT_NET_CONT_PER_CR_HR])).  Unfortunately, this is now giving me 3 times the amount I need to see (my dollar amount is 3.5 million when it should be 1.9 million).  Then I tried (sum({ Fixed [FISCAL_YR],[School Cde],[Institut Div Cde]: min([Credit Hrs])})*([STUDENT_NET_CONT_PER_CR_HR])).  That got me closer, but it is still not exact (it shows 1.8 million instead of 1.9 million).  I tried to break it down and for some reason none of the calculations are coming up quite right.  The ([STUDENT_NET_CONT_PER_CR_HR]) is half as much as it should be (this is based on another calculation -- SUM([IND_STUDENT_CONTRIBUTION].[STUDENT_NET_CONTRIBUTION]/[IND_STUDENT_CONTRIBUTION].[YR_CREDIT_HRS]) and my count of student ID is twice what it should be.

             

            Do you have any suggestions?