Forum Discussion

Merchant23's avatar
Merchant23
New Member
4 years ago
Solved

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 s...
Show and Tell
  • v-yalanwu-msft's avatar
    v-yalanwu-msft
    4 years ago

    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.

v-yalanwu-msft's avatar
v-yalanwu-msft
Community Support
4 years ago

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.