17 Replies Latest reply: Mar 25, 2013 5:16 AM by Gaurav Tyagi

# average of rows in a column

Hi all.
I have a table as this:
1 dimension: Year
3 expression: Employees, Cost, NewValue
YearEmployeesCostNewValue
20114130014400
20125270018000
20137680025200
I want calculate the colum NewValue. It is given: avarege of all the value in the column cost multiplied by each value of the column Employees.
14400 = (1300+2700+6800)/3 * 4
18000 = (1300+2700+6800)/3 * 5
25200 = (1300+2700+6800)/3 * 7

any ideas?
Thanks!
• ###### Re: average of rows in a column

Hi

Something like this:

=Avg(Total Cost) * Only(Employees)

OR

=Avg(Total Cost) * Sum(Employees)

Hope that helps

Jonathan

• ###### Re: average of rows in a column

=Avg(Total Cost) * Only(Employees) no work

=Avg(Total Cost) * Sum(Employees) give me no correct value

• ###### Re: average of rows in a column

(Sum(TOTAL Cost)/Count(TOTAL Cost))*Employees

• ###### Re: average of rows in a column

no work

• ###### Re: average of rows in a column

Hi,

Have a look at the application attached here with.

If this is not how your data structure is then kindly upload the qvw file.

Regards,

Kaushik Solanki

• ###### Re: average of rows in a column

Your solution works. But the field Employees and Cost are 2 expressions.
Employees = count(person)

Cost = sum(costs) / sum(count(person))

If I try

avg(all sum(costs) / sum(count(person))) * sum(count(person)

no work!

• ###### Re: average of rows in a column

=Avg(Total(cost))*Employee

• ###### Re: average of rows in a column

Hi

=sum(avg(total(cost)))*employee

try this, it will work.

Anish

• ###### Re: average of rows in a column

I supose in table you have Sum(Cost) and Count(Employees);

If true,try:

=sum(total Cost) / count(total distinct Year) * count(Employees)

• ###### Re: average of rows in a column

If in expression you have Cost and Employees like is seen in table(no aggr functions like sum,count) try:

=sum(total Cost)/count(total distinct Year) * Employees

• ###### Re: average of rows in a column

Hello,

This should work perfect.

=Avg(Total Cost)*Employees

Regards,

Manideep

• ###### Re: average of rows in a column

Your solution works. But the field Employees and Cost are 2 expressions.
Employees = count(person)

Cost = sum(costs) / sum(count(person))

If I try

avg(all sum(costs) / sum(count(person))) * sum(count(person)

no work!

• ###### Re: average of rows in a column

Did You tried with this?

=\$(=Sum(costs) / Count(person)) * count(person)

Celambarasan

• ###### Re: average of rows in a column

Here is the solution you need,

Year, Persons, Cost

2011, 2, 650

2012, 2, 1350

2013, 5, 3400

2011, 2, 650

2012, 3, 1350

2013, 2, 3400

];

Take Year as a Dimension in straight table and add following 4 expressions:

Employees: =Sum(Persons)

Actual Cost:=Sum(Cost)

AverageCost:=Aggr(Sum(Total Cost),Year)/3

Final:=AverageCost*Employees.

After this right click on properties-->Presentation tab--> Select AverageCost Column and select  Hide Column option.

You can also set No Totals option to every column.

Here is the Output Table

Year Employees Actual Cost Final
2011 4 1300 14400
2012 5 2700 18000
2013 7 6800 25200

Regards

Manideep

• ###### Re: average of rows in a column

Hi,

Check with this

=\$(=Sum(Cost)/ Count(Distinct Year))* Count(Distinct Employees)

Hope it helps

• ###### Re: average of rows in a column

Hi,