4 Replies Latest reply: May 12, 2011 6:30 AM by Erika Jain RSS




      I have a Pivot table used to display the number of hours an Employee works over a number of weeks. This is in the format of:

                                                   Week |           1      2      3      4      5 ...

      Employee Employee Name                    10     20    10    10    10

      The dimensions are: Employee ID, Name, Week_no


      The expression is a sum of hours that count towards actual working hours: sum(total_hours_attended)

      What I would like to be able to do, is to select a number of weeks (in this case 5) and at the end of the row, provide an overall average, e.g:

                                                    Week |          1     2     3     4     5     Average

      EmployeeEmployee Name                      10   20   10   10   10        12


      If I create another expression to provide an average, it will average each week. Is there a way to have the average calculate at the end of the line?


      Thanks in advance


        • Averages



          A way to do that is :


          if( columnNo() = 0 , sum(total_hours_attended)/count(distinct week) ,  sum(total_hours_attended) )



          • Averages
            Dennis Hoogenboom

            Hi ben,


            I know one way to do this ,  but only if the hours in your expression is not a Sum().


            If your expression is just [Hours] you can change this to AVG[Hours] , this will return the average on every line but this will be the same if you don't use a calculation in your expression (fe: AVG(10) = 10)

            So this way you have the right hours in your expresion.

            Now go to the tab Presentation and now check "Show Partial Sums" for Employee Name.


            Hope this works for you.

            • Re: Averages
              Patrick Laredo



              if you use as expression



              sum(hours)/count(distinct Week)



              and on the presentation tab you tick "show partial sums" for Week and change the "Label for Totals" to "avg" for the same


              you will get the following



              • Re: Averages


                I have used Bcuisiniere's code and implemented what you wanted.