Hi, I'm doing an online advertising analysis, I want to create a set that contains the keywords have high ad costs and high clicks. By "high", I mean higher than Average 1.2 times.
I think it should be created, by the condition field within creating sets. I tired my formula, but it doesn't work, my formula is:
sum([costs])>average(total([costs]) * 1.2 and sum([clicks])>average(total(clicks]) *1.2 However, it gave me a error called "unknown function total called"
Can someone help me to write a correct formula?
You cannot use table calcs (Total) in your set formulas. In fact you couldn't even create that calc outside a set as you cannot aggregate Total.
A way around this might be to create an LOD fixed calc that gives you the average at a row level, then use that calculated field in your set formula. eg. {fixed [dimension] : sum([costs])/count([ads])}