Average only if > zero

[Anonymous]

How do i get this logic to excluded rows with Zero?

List.Average(Table.SelectRows(
DaysonHandData,
(InnerTable) => InnerTable[Product] = [Product])[Daily Average])

[amitchandak]

@Anonymous , You need to try like

List.Average(List.RemoveItems(#"Table Name"[Product],{0}))

[Anonymous]

Do i get rid of all the other code?

List.Average(Table.SelectRows(
DaysonHandData,
(InnerTable) => InnerTable[Product] = [Product])[Daily Average])

[Anonymous · Accepted solution]

Hi @Anonymous ,

You can try to add a filter in Table.SelectRows.

List.Average(Table.SelectRows(
DaysonHandData,
(InnerTable) => InnerTable[Product] = [Product] and [Daily Average] <>0)[Daily Average])

Here I create a sample to have a test.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUTJWitWBsAzgLBM4yxTMcoKLOcHVobLMlGJjAQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Product = _t, #"Daily Average" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Product", type text}, {"Daily Average", Int64.Type}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", 
    each 
    let _Product = [Product] in
    List.Average(
    Table.SelectRows(
    #"Changed Type",
    each _Product =[Product]and [Daily Average] <>0 )[Daily Average]))
in
    #"Added Custom"

Result is as below.

Best Regards,
Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Thank you!