Average only if > zero

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.