How do I only perform a calculation when both values are present?
Hi guys,
I need to subtract 2 different product prices from another and divide the difference (gap) by the total count of outlets that stock both of these 2 products. The criteria is that both products must have values captured against them for the calculation to display a proper figure. If either of the 2 prices are missing, that outlet should be skipped and not included in the final division. I've used Suppress When Value Is Null, but when either of the values are present, the dimension will pick it up and ignore this function even when I hide the values which are present by using IF statements in both expressions. Note that I use Set Analysis to only return the price related to the specific product.
The calculation:
(product1_price - product2_price)/count(outlets)
How do I only perform a calculation when both values are present so that I can properly aggregate and roll up on the different levels such as District and Sales Team?
