I want to calculate the average age of the individual employees. When I take the average value of the Age column then Employee 1 and 4’s ages are twice in data, giving me an average age of 41.2 .

I need a calculation that first get the Age value for distinct Employee then averages those Age values. This should give me an average age of 40.3.

Ok so you basically could have duplicate rows. This would cause an issue with my previous approach because the duplicated rows could all have rank=1 and then the average would be incorrect.

Another approach could be to divide age by the number of rows for each employee. If we sum all these and divide by the count (distinct) of employees, then we would be working out the average.

Can you check if this works:

SUM( age / countOver(age, [{Employee}, {Employment}], PRE_AGG) )
/
DISTINCT_COUNT({Employee})

Thanks @darcoli, Your suggested formula needed a slight change. It helped me to move in the right direction.
The below formula worked for me. So, instead of doing partition by both Employee and Employment. Need to partition by Employee only.

SUM( Age / countOver(Age, [Employee], PRE_AGG) )/distinct_count(Employee)

Hi, thank you so much, i was able to use your formula.
Do you maybe know how to create a calculated measure to get a Percentage of overlapping sets -i.e. the sets are not mutually exclusive. I have described my issue here:

I don’t think QuickSight is able to do it.
Many thanks in adavance!