I have a parameter with options MoM, YoY, vs Budget...etc, so that when I select either of them, the variance for that selected option will pop up in a table format (i.e. there are 2 calculated columns in the table: variance & variance %).

I want to show "N.M." as the variance % if either current or the prior period has negative values. How to do that?

FYI, below is my current formula for the calculated field "vs Basis" parameter:

IF [vs Basis]='vs BUD MTD' THEN ((IFNULL(SUM(IF month([Period])=[Month] AND [Scenario]='Net Actual' THEN [Value (HKD)] END),0))/(IFNULL(SUM(IF month([Period])=[Month] AND [Scenario]='Total Budget' AND [vs Basis]='vs BUD MTD' THEN [Value (HKD)] END),0))-1

)

ELSEIF [vs Basis]='MoM' THEN ((IFNULL(SUM(IF month([Period])=[Month] AND [Scenario]='Net Actual' THEN [Value (HKD)] END),0))/(IFNULL(SUM(IF month([Period])=[Month]-1 AND [Scenario]='Net Actual' THEN [Value (HKD)] END),0))-1

)

ELSEIF [vs Basis]='YoY' THEN ((IFNULL(SUM(IF YEAR([Period])=[Year] AND MONTH([Period])<=[Month] AND [Scenario]='Net Actual' THEN [Value (HKD)] END),0))/(IFNULL(SUM(IF YEAR([Period])=[Year]-1 AND MONTH([Period])<=[Month] AND [Scenario]='Net Actual' THEN [Value (HKD)] END),0))-1

)

ELSEIF [vs Basis]='vs BUD YTD' THEN ((IFNULL(SUM(IF YEAR([Period])=[Year] AND MONTH([Period])<=[Month] AND [Scenario]='Net Actual' THEN [Value (HKD)] END),0))/(IFNULL(SUM(IF YEAR([Period])=[Year] AND MONTH([Period])<=[Month] AND [Scenario]='Total Budget' AND [vs Basis]='vs BUD YTD' THEN [Value (HKD)] END),0))-1

)

END