Calculating average in row totals without taking zeros in count

[sofypond]

Hello,

I have a table on Looker where I have added the Totals option from the Explore view.

As I am calculating percentages, Looker is return the mean or average in the Totals row, as expected. The issue comes when I have zeros on any of the rows, which is calculating the average by counting those zeros. For example, in the following image, the average should be 18.14%, not 5.85%.

I have tried creating a table calculation to replace the zeros with nulls, but it is still not working:

The formula that I am using in LookML is the following one:

 measure: d30_net_total_roas {
    type: number
    sql: safe_divide(${d30_net_total_revenue},${spend}) ;;
    description: "Day 30 ROAS %"
    label: "Day 30 Net ROAS %"
    group_label: "DX Total ROAS %"
    value_format_name: percent_2

How can I fix this so it doesn’t count zeros or nulls to calculate the average in the totals row?

Thanks!

[quynh]

Have have tried this? 
 

Filter nulls 

[jiaqitang97-164 · Accepted solution]

When you edit the field, you can filter “D30 ROAS” to “is not null” and “is not equal to” 0.