average of rows in a column

[#1]

Hi all.

I have a table as this:

1 dimension: Year

3 expression: Employees, Cost, NewValue

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

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!

[manideep78]

Hello,

This should work perfect.

=Avg(Total Cost)*Employees

Please find attached qvw

Regards,

Manideep

[CELAMBARASAN]

Hi,

Check with this

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

Hope it helps

[#4]

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!

[#5]

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!

[CELAMBARASAN]

Did You tried with this?

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

Celambarasan

[narender123]

Hi,

Tis could help you.

[manideep78]

Here is the solution you need,

LOAD * INLINE [

    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

Please find the attached qvw.

Regards

Manideep

[#9]

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