how to find max value using m language

[fab196]

dataset

id	sales amount	index	custom
1	200	1	1
1	3000	1	2
2	400	2	1
2	200	2	2
2	300	2	3

result would be like

id	sales amount	index	custom	cum_sales
1	200	1	1	500
1	300	1	2	300
2	400	2	1	900
2	200	2	2	500
2	300	2	3	300

suppose index id 2 having custome id is 1,2,3 respec

if index number 2=max(customid ) then amount of for this row will remain same as of sales amount .then this amount will going to add in descding order for that index id 

for example index id 1 and 2 is there .for index id 2 having 3 is max cutom id 

so for the calculation for this row wil be 300 amount but for the other row of index id 2 would become 3+2+1=500 nad 500+400=900

how to  I achive this result using m languge or power query

please help

@amitchandak 

@ImkeF 

@themistoklis 

[v-kelly-msft · Accepted solution]

Hi @fab196 ,

Use below M codes:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUTIyMACShmAcqwMRM4aLGYHFjIAsE7CYEVydEVyvEYo6Y7iYsVJsLAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [id = _t, #"sales amount" = _t, index = _t, custom = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"id", Int64.Type}, {"sales amount", Int64.Type}, {"index", Int64.Type}, {"custom", Int64.Type}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"id"}, {{"Max", each List.Max([custom]), type nullable number}, {"details", each _, type table [id=nullable number, sales amount=nullable number, index=nullable number, custom=nullable number]}}),
    #"Expanded details" = Table.ExpandTableColumn(#"Grouped Rows", "details", {"id", "sales amount", "index", "custom"}, {"details.id", "details.sales amount", "details.index", "details.custom"}),
    #"Removed Columns" = Table.RemoveColumns(#"Expanded details",{"details.id"}),
    #"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"details.sales amount", "sales amount"}, {"details.index", "index"}, {"details.custom", "custom"}}),
    #"Added Index" = Table.AddIndexColumn(#"Renamed Columns", "Index.1", 1, 1, Int64.Type),
    #"Added Custom" = Table.AddColumn(#"Added Index", "Custom.1", each let 
value=#"Added Index"[sales amount]
in 
if [custom]=[Max] then [sales amount] else List.Sum(List.Range(value,[Index.1]-1,[Max]-[custom]+1))),
    #"Removed Columns1" = Table.RemoveColumns(#"Added Custom",{"index", "custom", "Index.1"})
in
    #"Removed Columns1"

And you will see:

For the related .pbix file,pls see attached.

Best Regards,
Kelly

Did I answer your question? Mark my post as a solution!

[fab196]

thanks Kelly ...your solution solve my probelm...thanks a lot.