Forum Discussion
How to return value based on MAX in separate column
Hello,
I am hoping to find a formula to help me SUM my on hand units, based on the most recent (greatest) week number. This is part of a larger formula determining which data set to use, so the simpler the better!
In this case I'd want to Sum the 'Wkly_Inv_qty' based on the Max value in 'Week Number'.
So the greatest week is 14, and a Sum on the qty by week 14 would be 423 (144+85+23+171)
Thank you for helping!
Hi, Merchant23 ;
In power query.
= Table.AddColumn(#"Changed Type", "Custom", each if [Week_ Num]= List.Max( #"Changed Type"[Week_ Num]) then 1 else null)= Table.AddColumn(#"Added Custom", "Custom.1", each List.Sum(Table.SelectRows(#"Added Custom",each [Custom]=1)[Wky]))the final show:
The M language.
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("hZC7DcUwCAB3oU7BzwHPYmX/NV4MkaF7HWfJd4i1AOECg+dawP6OHqPpfp4x3zzfmTBA5thA+cF4AweQB0iC6gYNqAKeABOfgKBXgFkqQK4tQKMCPv74tfaXsmvbXu5yC5Y6F0g113GOWqzdhrqcuh2t9Jzw3cboCzw/", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Wky = _t, #"Week_ Num" = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Wky", Int64.Type}, {"Week_ Num", Int64.Type}}), #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each if [Week_ Num]= List.Max( #"Changed Type"[Week_ Num]) then 1 else null), #"Added Custom1" = Table.AddColumn(#"Added Custom", "Custom.1", each List.Sum(Table.SelectRows(#"Added Custom",each [Custom]=1)[Wky])), #"Removed Columns" = Table.RemoveColumns(#"Added Custom1",{"Custom"}) in #"Removed Columns"In dax.you could create the measure.
dax = CALCULATE(SUM('Table'[Wky]),FILTER(ALL('Table'),[Week_ Num]=CALCULATE(MAX('Table'[Week_ Num]),ALLSELECTED('Table'))))The final show:
Best Regards,
Community Support Team _ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
2 Replies
Hi Merchant23 ,
Not sure what exactly your expected output is, but you can try something like this :I've only taken the first 2 weeks in the sample data
Sum Max Qty =var _maxwk = CALCULATE(MAX(Inv[Week_Num]),ALL(INV))var _sum =CALCULATE(SUM(Inv[Wkly_Inv_qty]),FILTER(allselected(INV), Inv[Week_Num] = _maxwk))RETURN_sumKind regards,
Rohit
Please mark this answer as the solution if it resolves your issue.
Appreciate your kudos! 🙂Hi, Merchant23 ;
In power query.
= Table.AddColumn(#"Changed Type", "Custom", each if [Week_ Num]= List.Max( #"Changed Type"[Week_ Num]) then 1 else null)= Table.AddColumn(#"Added Custom", "Custom.1", each List.Sum(Table.SelectRows(#"Added Custom",each [Custom]=1)[Wky]))the final show:
The M language.
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("hZC7DcUwCAB3oU7BzwHPYmX/NV4MkaF7HWfJd4i1AOECg+dawP6OHqPpfp4x3zzfmTBA5thA+cF4AweQB0iC6gYNqAKeABOfgKBXgFkqQK4tQKMCPv74tfaXsmvbXu5yC5Y6F0g113GOWqzdhrqcuh2t9Jzw3cboCzw/", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Wky = _t, #"Week_ Num" = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Wky", Int64.Type}, {"Week_ Num", Int64.Type}}), #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each if [Week_ Num]= List.Max( #"Changed Type"[Week_ Num]) then 1 else null), #"Added Custom1" = Table.AddColumn(#"Added Custom", "Custom.1", each List.Sum(Table.SelectRows(#"Added Custom",each [Custom]=1)[Wky])), #"Removed Columns" = Table.RemoveColumns(#"Added Custom1",{"Custom"}) in #"Removed Columns"In dax.you could create the measure.
dax = CALCULATE(SUM('Table'[Wky]),FILTER(ALL('Table'),[Week_ Num]=CALCULATE(MAX('Table'[Week_ Num]),ALLSELECTED('Table'))))The final show:
Best Regards,
Community Support Team _ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
