Have only max value in same category

[yellow_old_5]

Hi , I need help with this one, i have 3 column, id, category and quantity, i want have a calculate column that have only one max value and rest 0 look at screenshot:

			want Output
ID	Category	Quantity	Max quantity
1	A	1	0
2	A	1	0
3	A	3	3
4	A	2	0
5	A	1	0
6	B	1	0
7	B	1	0
8	B	2	2
9	B	2	0
10	B	1	0
11	C	1	0
12	C	2	0
13	C	2	0
14	C	2	0
15	C	4	4

I want have it as calculate column if possible.

[v-shex-msft · Accepted solution]

Hi @yellow_old_5,

You can try to use the below measure formula to get and compare with the max quantity based on category groups and use the 'if statement' to replace the not match row values to zero.

formula =
VAR currQuantity =
    MAX ( Table[Quantity] )
VAR maxQuantity =
    CALCULATE (
        MAX ( Table[Quantity] ),
        ALLSELECTED ( Table ),
        VALUES ( Table[Category] )
    )
RETURN
    IF ( currQuantity = maxQuantity, currAmount, 0 )

Regards,

Xiaoxin Sheng