How to calculate average based on multiple dimensions

[salmany]

Hi,

I would really appreciate for the guidance on the following scenario.

I have 4 columns Emp No, Designation, Salary and Year. And i want to calculate the average salary by designation for each year. But when I display Avg(Salary) measure against Designation and Year dimensions in a table on front end, I am getting same average salary value against all years for a particular designation.

Attached is the sample data with desired and current results i am getting.

Thanks.

[Lisa_P]

This is not normal behaviour, what details are you not telling us?

[salmany]

I have tried to explain the scenario in detail and provided the completed information. Appreciate if you can point me in the right direction.

Thanks

[Frank_Hartmann]

1:
LOAD * INLINE [
    Designation, Salary, Year, Emp.Code
    Ayah, 17572, 2020, 10
    Ayah, 19218, 2020, 11
    Ayah, 15135, 2019, 10
    Ayah, 16067, 2019, 11
    Ayah, 14135, 2018, 10
    Ayah, 16067, 2018, 11
    Cleaner, 17572, 2020, 7
    Cleaner, 17572, 2020, 31
    Cleaner, 15440, 2019, 7
    Cleaner, 15440, 2019, 31
    Cleaner, 15750, 2019, 35
    Cleaner, 14440, 2018, 31
    Cleaner, 15750, 2018, 35
    Cleaner, 15440, 2018, 46
];

left join

load Designation,Year, sum(Salary)/count(Designation) as avg Resident 1 Group by Designation,Year;

[salmany]

Thanks Frank.

But cannot we achieve the same result from front end  (i.e. app editor) instead of writing script in data loader?

[Frank_Hartmann]

try this as expression:

sum(Salary)/count(Total<Year,Designation>Salary)

[salmany]

Thanks everyone.

I got the desired output.