Hi,
I'm calculating percentage margin. The formula is as simple as:
margin (percentage) = margin (value) / sales (value)
But when sale (value) <= 0 or sale cost = 0 the margin (percentage) makes no sense (it gives huge negative numbers, which disturbs analysis). So I want to get rid of that. I did that by following:
margin (percentage) = if( sales (value) <= 0 or sale cost = 0, null, margin (value) / sales (value))
I almost works. Almost because that null value i put, doesn't count as missing value somehow (therefore when i uncheck "Include null values" in any dimension on graph/chart - they are included nonetheless).
2 dimensional heatmap shows that perfectly (black with "-" are my NULLs, while blank, white squares are actual missing values (no sales within current month for given dimension, which is product manager in that case):
Any ideas how to make my NULLs act like actual missing values?