Retrieve a value in one column based on MAX in another column

[VikasMattela]

Hi,

I have multiple 3 columns in one table (Employee,Salary,Year). For each employee in same year there are multiple values. I need a measure a find an employee name in a selected year whose average salary is max.

Example: 

Sno          Emp          Values      Year                           

1	A	31	2021
2	B	20	2022
3	C	37	2023
4	D	42	2024
5	A	30	2021
6	B	34	2022
7	C	25	2023
8	B	12	2021
9	C	46	2022
10	D	35	2023
11	A	20	2024
12	C	40	2021
13	D	43	2022
14	A	45	2023
15	C	21	2024
16	B	49	2021
17	C	7	2022
18	D	28	2023
19	B	8	2021
20	C	12	2022
21	D	32	2023
22	A	27	2021
23	C	24	2022
24	D	14	2023
25	A	10	2024
26	C	37	2021
27	D	17	2022
28	A	7	2023
29	B	14	2021
30	C	15	2022
31	B	46	2023
32	C	32	2024
33	D	47	2021
34	A	28	2022
35	C	9	2023
36	D	10	2024
37	A	29	2021
38	C	34	2022
39	D	14	2023
40	A	23	2021

Solution I am thinking to create summarize Table like below

In measure if selected year is 20221,so I would like to get for 2021 C employee has Max Average Value.

[vanessafvg]

I am a little unclear what you are saying, when you say the average salary is max, are you wanting the max value for the year?   Where does the average come in.  Also when i look at the data you have provided it doesn't seem to match your bottom chart, please share your calculation.

To me it makers sense that you would use an iterator potentially with summary