Column with the maximum value of another according to condition

[Syndicate_Admin]

Hello!!

I need help with a calculation, I have a table with two fields, the first is an id that can be repeated in other rows, the second has the total amount that can be different in each row. What I need is to create a new field that rescues the maximum value for each ID and leaves me at zero those with the minimum value.

Example:

ID	Amount	Total
A	100	0
A	150	0
A	170	170
B	50	0
B	80	80
C	200	0
C	250	250
D	10	10

I hope you understand, thank you very much.

[Syndicate_Admin]

Nice day

Please your help, what would happen if the total amount is repeated in the same id the idea is to leave a single result

Thank you for your help

[v-angzheng-msft]

Hi @Syndicate_Admin ,

Try to create a measure or calculate column

Measure:

Measure =
VAR maxAmoout = CALCULATE(
    MAX('Table'[Amount]),FILTER(ALL('Table'),'Table'[ID]= MAX('Table'[ID]))
)
RETURN
IF(maxAmoout = SUM('Table'[Amount]),maxAmoout,0)

Calculate column:

Column =
VAR MaxAmount =
    CALCULATE (
        MAX ( 'Table'[Amount] ),
        FILTER ( ALL ( 'Table' ), 'Table'[ID] = EARLIER ( 'Table'[ID] ) )
    )
RETURN
    IF ( 'Table'[Amount] = MaxAmount, 'Table'[Amount], 0 )

Sample data:

Result:

Is this the result you want? Hope this is useful to you

Please feel free to let me know If you have further questions

Best Regards,
Community Support Team _ Zeon Zheng
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Jihwan_Kim]

Hi, 

Please try the below measure.

Maximum for each ID =
VAR currentID =
SELECTEDVALUE ( 'Table'[ID] )
VAR groupbytable =
FILTER (
GROUPBY (
ALL ( 'Table' ),
'Table'[ID],
"@maxamount", MAXX ( CURRENTGROUP (), 'Table'[Amount] )
),
'Table'[ID] = currentID
)
RETURN
IF (
SELECTEDVALUE ( 'Table'[Amount] ) = SUMX ( groupbytable, [@maxamount] ),
SUMX ( groupbytable, [@maxamount] ),
0
)

Hi, My name is Jihwan Kim.

If this post helps, then please consider accept it as the solution to help other members find it faster.

[Vera_33 · Accepted solution]

Hi @Syndicate_Admin 

If you need a column in M

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUTI0MFCK1YGyTZHY5hC2E5BtimBaQJjOQKaRARIbqsQFbKJSbCwA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ID = _t, Amount = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"ID", type text}, {"Amount", Int64.Type}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"ID"}, {{"Total_m", each List.Max([Amount]), type nullable number}}),
    #"Merged Queries" = Table.NestedJoin(#"Changed Type", {"ID"}, #"Grouped Rows", {"ID"}, "Grouped Rows", JoinKind.LeftOuter),
    #"Added Custom" = Table.AddColumn(#"Merged Queries", "Total_m", each if [Amount]=[Grouped Rows][Total_m]{0} then [Grouped Rows][Total_m]{0} else 0)
in
    #"Added Custom"

Or DAX column

Total = 
VAR CurID = 'Table'[ID]
VAR MaxAmount = MAXX(FILTER('Table','Table'[ID]=CurID),'Table'[Amount])
RETURN
IF('Table'[Amount]=MaxAmount,'Table'[Amount],0)

[Syndicate_Admin]

Thanks a lot!!! I used the DAX column and it worked perfectly.