how to find max value using m language

Question

dataset&nbsp;idsales amountindexcustom1200111300012240021220022230023&nbsp;result would be like&nbsp;idsales amountindexcustomcum_sales120011500130012300240021900220022500230023300&nbsp;suppose index id 2 having custome id is 1,2,3 respecif 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&nbsp;for example index id 1 and 2 is there .for index id 2 having 3 is max cutom id&nbsp;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=900how to&nbsp; I achive this result using m languge or power query&nbsp;please helpamitchandak&nbsp;ImkeF&nbsp;themistoklis&nbsp;

v-kelly-msft · Accepted Answer

Hi&nbsp;fab196&nbsp;,
&nbsp;
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.
&nbsp;
Best Regards,Kelly
Did I answer your question? Mark my post as a solution!

Forum Discussion

how to find max value using m language

2 Replies

Related content

M Language Transformations

Changing Your Community Language Preferences

PowerBI - Matrix table, hide values used only for a visual calculation

multi language

Language Translation

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