Forum Discussion

doug_morris's avatar
doug_morris
Frequent Visitor
3 years ago
Solved

List.Average calculation problem

List.Average is not computed correctly in this code.

 

[
    Source = #table({"Value"}, {{null}, {1}}) & #table({"Value"}, {{2}}),
    Avg = List.Average(Source[Value])
]

 

It computes to 1 whereas it should be 1.5. Such incorrect List.Average calculation seems to occur anytime you are working with a non-buffered table built by combining tables. If you do an average on a column in the table like the code above or in a Table.Group, the average calculation erroneously includes nulls in the value count for the denominator. If you buffer the table or buffer the list before averaging, the error does not occur. I do not see this error with List.NonNullCount, List.Median, or List.StandardDeviation.

 

Has this been raised as a defect with the Power BI team? I've tested this behavior with multiple tables in both Power Query for Excel and Power BI and the defect shows up in all cases.

2 Replies