How to create a calculated field which is customized based on filter selection?

[JayKay1]

Hi there, I have created a custom drop-down list (using CASE WHEN formula) which groups campaigns together based on the country where they are present.  However, now I would like to create a custom statement for each selection of such: e.g. IF the selection of drop down = US then take the clicks (only from the US campaigns) and multiply them by static (custom) click to sale ratio (e.g. 1.5%) and further multiply this by constant sales value (e.g. 2000 USD) and subsequently discounting the cost of these campaigns. I would like to do this for each selection of the country drop down list so when I change the selection the new calculation that reflects in the graph uses values related to the group of campaigns (i.e. clicks and cost for US only as well as the constants – i.e. 1.5% & 2000 USD that are defined for the given region). What would be the best way to achieve this? So far I seem to be hitting ‘complexity limits’ and getting the error message which mentions that metrics cannot be combined with dimensions when creating a calculated field.

e.g.:

Case

when CONTAINS_TEXT(Country drop down,"us") then ((Clicks*0.015*2000)-Cost)

when CONTAINS_TEXT(Country drop down,"uk") then ((Clicks*0.018*1500)-Cost)

ELSE ((Clicks*0.012*1000)-Cost)

END