cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Helper II

## Have only max value in same category

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.

1 ACCEPTED SOLUTION
Community Support

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

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.
Community Support

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

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.