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.

rohit_singh's avatar
rohit_singh
Solution Sage
4 years ago

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
_sum

Kind regards,

Rohit


Please mark this answer as the solution if it resolves your issue.
Appreciate your kudos! 🙂