If you edit your calculated field to the following;
IF [Expense-Income Variance %]>1.5 OR [Expense-Income Variance %]<-1.5 OR ISNULL([Expense-Income Variance %]) THEN '✔' ELSE ' ' END
Would this satisfy your requirement?
You won't be able to work around the Exp Current Month because even if you pick it up as '0' instead of 'Null', you're doing a division operation which will give you a Null if you try dividing by zero.
Let me know if I'm missing something here.
Hi Marc-Anthony. Thanks for responding to my question. I marked it correct, however I am not quite getting the effect I would like, and I thought maybe you would have a suggestion.
In this screen shot, there are three data rows that I would not want to display the KPI, as the current month and prior 3 mo average are = $0.
Perhaps my KPI calcuation is not the issue here?
Could it be my variance calculations are the problem?
Thanks for any suggestions you have have. If needed I can repost this in a new discussion.
Based on the info provided I think this is what you're looking for - basically I'm checking if there is an amount in the prior month expenses that exists, then moving towards the logic checking based on the values and variance. Please see the output below;
Here's the calc field for your reference;
IF(ATTR([Exp Current Month])>0 OR
ATTR([Exp Prior Mo])>0 OR
ATTR([Exp Prior Mo 2])>0 OR
ATTR([Exp Prior Mo 3])>0)
IF [Expense-Income Variance %]>1.5 OR
[Expense-Income Variance %]<-1.5 OR
ISNULL([Expense-Income Variance %])
ELSE ' '
ELSE ' '
Since you're not using another symbol for the ones that don't hit your criteria, you could just change the test color of the symbol? Right click the checkmark -> format -> change color of default text.
If you were using another symbol, you probably would run into some difficulty trying to conditionally format the column - crosstabs are always very tricky to use in Tableau as you want to use them like an excel based tool, however you must remember Tableau is meant for data visualization not spreadsheets.