median based on distinct values in another column

[skutovicsakos]

Hello,

I have data that looks like this:

Employee	Employment	Age
1	a	35
1	b	35
2	a	40
3	a
4	a	51
4	b	51

I want to calculate the median age of the individual employees. Simply taking the median of age column is not good, since it counts Employee 1 and 4's ages twice.

I need a measure that counts distinct values in the employee column, then takes their median age. (Employees with blank age shouldn't be calculated)

Any idea about a formula that could do this?

Thanks!

[MattAllington · Accepted solution]

I think this should work

calculate(Medianx(values(table[employee]),calculate(average(table[age]))),table[age]>0)