Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
Employee | Employee 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
Ben
Hi,
A way to do that is :
if( columnNo() = 0 , sum(total_hours_attended)/count(distinct week) , sum(total_hours_attended) )
Benoît
Hi,
A way to do that is :
if( columnNo() = 0 , sum(total_hours_attended)/count(distinct week) , sum(total_hours_attended) )
Benoît
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.
hi,
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
Hi,
I have used Bcuisiniere's code and implemented what you wanted.