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

# Averages

Hi,

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?

Ben

• ###### Averages

Hi,

A way to do that is :

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

Benoît

• ###### Averages

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

hi,

if you use as expression

[Code]

sum(hours)/count(distinct Week)

[/Code]

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

Hi,

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