Set analysis and ALL/TOTAL

[lisabarelle]

Hi all,

I'm trying to compute the fraction of a subset of sales. I have one dimension called 'sales', in which I want to focus on north america. As I drill down (using other dimensions like state, sales person, etc.), I'd like the fraction to consider the current selection (on the top), but disregard the selection (but not the set analysis) on the bottom. Makes sense?  

I've tried the following with no success:

sum({<[Country]={'North America'}>}[SALES]) /  sum(TOTAL {<[Country]={'North America'}>}[SALES])

If I restrict my dataset to 'North America' (just deleting the values in the table), this works:

sum([SALES]) /  sum(TOTAL [SALES])

But obviously, I need to be able to use it with set analysis.

Thanks

[lisabarelle]

Excellent, thanks. Def moving in the right direction. Is there a wild card I could use in the second set analysis? To disregard everything BUT 'COUNTRY'? Something like:

sum(TOTAL {<[COUNTRY]={'North America'}, [*]>} [SALES])

[marcus_sommer]

A wildcard for the fields is not available else all fields needs to be explicitly specified whereby it could be in general shortcut with a concat-function within the set analysis, like { < Field = {'x'}, $(='[' & concat($Field, '],[') & ']') >}. Within the concat() could be further conditions like a set analysis for certain $Table and those which shouldn't be included might be excluded with a replace() statement.

However in your case you could get it probably more easier with the 1 as identifier, like:

sum(TOTAL {1<[COUNTRY]={'North America'}>} [SALES])

- Marcus