cancel
Showing results for
Did you mean:

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

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.