Calculate avg by dimension

itdepartment — Mon, 29 Mar 2021 16:28:55 GMT

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:

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

Very appreciate with your answers!

Re: Calculate avg by dimension

MayilVahanan — Tue, 30 Mar 2021 01:55:42 GMT

Try like below

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

itdepartment — Tue, 30 Mar 2021 09:05:33 GMT

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! 🙂