Grap first value above running total

[Anonymous]

Hi

I have the below table that shows the running total for a given values.

I want the query to check for the first value of VOR when running total exceeded the capacity.

[wdx223_Daniel]

M Code

NewStep=Table.Group(PreviousStepName,"WM",{"VOR",each Table.Skip(_,each [Capacity]>=[#"Running Total"]){0}?[VOR]?})

DAX Code

NewMeasure=FIRSTNONBLANK(VALUES[Table[WM],IF(CALCULTE(MAX(Table[Capacity])<MAX(Table[Running Total])),1))

[mussaenda]

Hi @Anonymous ,

Have you tried the if else condition?

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("dc47EoAwCATQu1Cn4JNAcghri0zufw1RR4XCArZ4xe6csG9IUMD82BDPwGqwykvMwZpqMgmmPZmMYJ0kWqPPRKQ9dlf5I8QLbeD4xU4joVBAz85JJSqjb4iqUaua710H", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [WM = _t, VOR = _t, CAPACITY = _t, #"RUNNING TOTAL" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"WM", type text}, {"VOR", Int64.Type}, {"CAPACITY", Int64.Type}, {"RUNNING TOTAL", Int64.Type}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each if [RUNNING TOTAL] > [CAPACITY]
or [RUNNING TOTAL] > [CAPACITY]
then 1 else 
0),
    #"Grouped Rows" = Table.Group(#"Added Custom", {"WM", "Custom"}, {{"Count", each _, type table [WM=nullable text, VOR=nullable number, CAPACITY=nullable number, RUNNING TOTAL=nullable number, Custom=number]}}),
    #"Added Custom1" = Table.AddColumn(#"Grouped Rows", "Custom.1", each Table.AddIndexColumn([Count], "Index", 1, 1)),
    #"Expanded Custom.1" = Table.ExpandTableColumn(#"Added Custom1", "Custom.1", {"VOR", "CAPACITY", "RUNNING TOTAL", "Index"}, {"VOR", "CAPACITY", "RUNNING TOTAL", "Index"}),
    #"Added Custom2" = Table.AddColumn(#"Expanded Custom.1", "Custom.1", each if [Custom] = 1
and [Index] = 1 
then [VOR]
else null),
    #"Removed Other Columns" = Table.SelectColumns(#"Added Custom2",{"WM", "VOR", "CAPACITY", "RUNNING TOTAL", "Custom.1"})
in
    #"Removed Other Columns"

[watkinnc]

Just GroupBy "WM" and choose the Max aggregation for Running Total.

--Nate

[Anonymous]

Unfortunately, it didn't work!

This is not max, but above certain value! and I want the corresponded column value.