Calculate avg by dimension

[itdepartment]

Hi community! Very greatful if someone can help my with this situation.

In the example below, I try to obtain the correct average. This is my dataset:

LOAD * INLINE [
Employee, Month, %WorkingDay, Salary
A, jan, 50, 200
A, feb, 75, 200
B, jan, 100, 300
B, feb, 100, 300
C, jan, 100, 300
];

I expect to obtain the correct value of Salary average (200+300+300)/3 = 266,66. In my expression, I use set analysis and the result make sense

When I add %WorkingDay dimension, one value of Salary disappears on row 2 but the total result is still ok.

I would like to show the table with all data, like this:

Employee	%WorkingDay	avg(aggr(Salary, Employee))
		266,67
A	50	200,00
A	75	200,00
B	100	300,00
C	100	300,00

I try to modify set analysis expression adding %WorkingDay dimension, but the total result now is not correct.

Very appreciate with your answers!

[MayilVahanan · Accepted solution]

HI @itdepartment 

Try like below

If(Rowno() = 0, Avg(Aggr(Avg(Salary), Employee)), Avg(Salary))

[itdepartment]

Hi MayilVahanan, it works!

I made a change in your expression: RowNo(Total) instead of RowNo() because my object is a straight table. For pivot table RowNo() is ok.

Thanks for your answer! 🙂