There seems to be a consistant bug where Metabase fails to exclude null values from averages, despite multiple tries.
The issue is that Metabase automatically converts null values for numeric columns when doing an average. It seems to retain values as well even when applying filters.
Methods tried at the card level:
average(coalesce(number_column_with_nulls, ""))
average(case(number_column_with_nulls>0, number_column_with_nulls))
I also tried to replicate my card as an SQL query in Metabase and I got difference results from Snowflake. This is a similar issue to previous tickets which have been poorly answered:
2020 - Average with empty fields
2021 - NULL values/Blank fields
2022 - Finding rate within a column
What I am expecting here is a way for Metabase to exclude null values from aggregations. From what I have found in the documentation, this has to do with a specific limitation regarding custom expression aggregations. Specifically, it seems impossible for Metabase to exclude nulls from aggregations and instead it converts null values to 0 which is particularly impactful for averages.
For details:
The column's data type is a float, it has no semantic type in Metabase or formatting applied.