Forum Discussion
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.
Hi Anonymous,
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
1 Reply
Hi Anonymous,
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