To find Max value from table based on group

[alt_tush]

Hi, 

I have below set of data. i want to find out max value based on group level data.

Dept	Revenue
HR	10
HR	30
HR	20
IT	100
IT	200
IT	500
IT	300
ADMIN	3000
ADMIN	1000
ADMIN	2000

I am expecting below output in Max column to get "Y" & "N" next to their revenue based on dept level.

Dept	Revenue	Max
HR	10	N
HR	30	Y
HR	20	N
IT	100	N
IT	200	N
IT	500	Y
IT	300	N
ADMIN	3000	Y
ADMIN	1000	N
ADMIN	2000	N

Thank you for the help. 

[DataNath]

@alt_tush here's how I'd go about this - basically just use a summarize to find the max for each group, before utilising a join to see whether or not each revenue matches the max for that department. We then just flag the two sets of results and union them back together to finish - the sort and select were extra tools used to maintain the original order and then clean the layout back up.

[alt_tush]

Hi DatNath,

Thank you for your prompt reply.

Is there any way to avoid the join on Revenue column and get the same output. Because i don't want to join on revenue column as it is not the key field.

Thank you again :)

[DataNath]

No problem @alt_tush. Whenever a request like this comes up, the usual approach is to do it this way as the join only pulls out the actual max and allows us to easily flag the 2 output streams. Part of the join is also [dept] so it'll only look for the max in each relevant department rather than across all revenues.

As a worst case scenario, if you're really against the join, you could perhaps just append the max's to all records and use a filter to remove those that don't match, before adding a formula to put the flag in. However, this will blow your data up and lead to a drop in performance, especially if your dataset is large.

[binuacs]

@alt_tush One way of doing this is by assigning a record id to each field and joining based on the record id field