Show which rows in a group have a specific value

[mstoppelman]

Hi, my data is structured as below with my expected result (calculated column)

Group	Number	Calculate Column
a	1	y
a	2	y
a	3	y
b	1	y
b	2	y
b	3	y
c	1	n
c	2	n
d	1	n

so basically I want all the rows in each group to show "y" if one row in the group has the highest number in column b, and to show n if a group does not have the highest number.

my formula that I've tried is listed below, but it has not worked, any help is greatly appreciated

calculated column = if(calculate(countrows(table), group = earlier(group), number = max(number)) > 0, "y", "n")

[SamWiseOwl]

Hi I'm guessing at your table name, so replace list with your table name 

[mstoppelman]

Oh I see, that works now thanks so much!

[SamWiseOwl · Accepted solution]

Hi @mstoppelman 

Highest value y/n =

var CurrentGroup= List[Group] --Capture the current row value

var grouphigh = CALCULATE(max(List[Number]), all(list),List[Group] = CurrentGroup)
--Return the max number, unfilter table, refilter to where the group equals original group

return

if(

    grouphigh = max(List[Number]), "y", "n") --if the overall highest number equals the group high y/n
If this helped please mark it as a solution for others to find 🙂

[mstoppelman]

Hi, thanks for the reply. For the column syntax, is "List" a function? Or a name? When I try to enter the syntax in, PowerBi doesn't have List as a function