2 Replies Latest reply: Jan 17, 2013 9:38 AM by Georgina Spary RSS

    Aggregating averages -- aggr()?

    Georgina Spary

      Hello folks

       

      I am really stuck on this and was due to release the report today! so really hoping you can help.

       

      The attached shows an average score for students, against a heirarchy of dimensions: student number, programme, department, faculty. Each student has only one score. Each student is studying 2 programmes: BI and one other.

       

      I am trying to avoid double-counting the students at Faculty level while showing an accurate figure at programme and student level. see attached for 2 failed versions.

       

      Thanks in advance

       

      George

        • Re: Aggregating averages -- aggr()?
          Stefan Wühl

          Maybe like this?

           

          =Sum(Tariff)

          / Count(aggr( [Student Number]*[Valid Tariff Count],Faculty,Department, Programme, [Student Number]))

           

          edit:

          Above is probably not what you want, I missed the part with the not-counting duplicate Student Numbers.

           

          If you rework your data model so that the student number's tariff is a distinct table, I think your version A should match your requirement.

           

          Please check attached.