Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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.
Solved! Go to 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
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
User | Count |
---|---|
98 | |
87 | |
77 | |
67 | |
63 |
User | Count |
---|---|
110 | |
95 | |
95 | |
64 | |
58 |