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!
Solved! Go to Solution.
When you edit the field, you can filter “D30 ROAS” to “is not null” and “is not equal to” 0.