have only one max value in calculate column

[Anonymous]

Hi , I have 3 columns : Id, category and quantity, i want add a calculate columns that i have only one max value in same category, can i achive that with calculate columns?
Add screen shot what i want:

			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

[sanalytics]

@Anonymous 

You can use below formula also

Hope it will help you.

Regards

sanalytics

[SpartaBI]

@Anonymous 

MAX = 
VAR _current_qty = 'Table'[Quantity] 
VAR _current_cateogry = 'Table'[Category]
VAR _max_per_category = 
    CALCULATE(MAX('Table'[Quantity]), REMOVEFILTERS('Table'), 'Table'[Category] = _current_cateogry) 
VAR _result = 
    IF(_current_qty = _max_per_category, _current_qty, 0)
RETURN
    _result

Showcase Report – Contoso By SpartaBI

     

[Anonymous]

Hi thank you for your fast reply it doesnt work, as you can see in category b i have 2 max values and i only want one value.

[SpartaBI]

@Anonymous @ which one? 🙂 what is the desired logic for ties?

[Anonymous]

i want only one max value per category so if ties only one and the other one will be 0

[SpartaBI · Accepted solution]

@Anonymous 

MAX = 
VAR _current_qty = 'Table'[Quantity] 
VAR _current_cateogry = 'Table'[Category]
VAR _max_per_category = 
    CALCULATE(MAX('Table'[Quantity]), REMOVEFILTERS('Table'), 'Table'[Category] = _current_cateogry) 
VAR _id_with_max = 
    MINX(
        FILTER(
            'Table',
            'Table'[Category] = _current_cateogry 
            && 'Table'[Quantity] = CALCULATE(MAX('Table'[Quantity]), REMOVEFILTERS('Table'), 'Table'[Category] = _current_cateogry)
        ),
    'Table'[ID]
)

VAR _result = 
    IF(_current_qty = _max_per_category && 'Table'[ID] = _id_with_max, _current_qty, 0)
RETURN
    _result

Showcase Report – Contoso By SpartaBI