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

[brunobertels]

Hi 

May be this 

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

[lisabarelle]

Unfortunately, that sums across all countries (when leaving the field empty). 

Surprised there doesn't seem to be a straightforward answer to this given it must be something people do every day.

[marcus_sommer]

It depends on the scenario  + requirement which combination of expressions-features are useful to get the wanted results. For this is it necessary to differentiate between both measures. With set analysis it's possible to adjust and/or to overwrite and/or to ignore certain/all selections. With TOTAL instead it's possible to ignore all/certain dimensionalities within your charts.

Although it's not seldom that both features are used together within an expression they are quite different and complete independent to each other - each one has it's own purpose.

Therefore I suggest that you split your task and develop both requirements in parallel and in small steps - and if each part worked you combine them to more complex parts. It's much easier as to handle with complex expressions from the start and to detect what didn't work like intended.

Beside this I assume that your challenge is mainly related to a missing specifying which dimensionalities should be where ignored. This means you may need constructs like: sum(total < state, person > value) which means all dimensions unless the listed ones will be ignored. By more complex objects you may also need to differentiate on which dimensionalities you want to apply which kind of expressions - and this could be queried with dimensionality() and then branched to different expression(parts). 

- Marcus

[lisabarelle]

Hi @marcus_sommer ,

Thanks for the explanation. I fully understand that these are different and are context dependent. However, with a straightforward question, I'm still not sure what the answer is. Any formula or approach might be more helpful. 

[marcus_sommer]

Put all your needed dimensions within the intended order (usually hierarchically) within the pivot. And then n expressions starting with sum(Sales) then sum(total Sales) and then sum(total <Dim1> Sales) and then sum(total <Dim1, Dim2> Sales) and so on. The results should now show how totals are working and you could combine them for your rate-calculation.

- Marcus

[lisabarelle]

Thanks for this. 

Let me maybe clarify: 

I've tried to do exactly what you are describing above 

1. sum(Field)
2. sum(ALL Field)
3. sum(TOTAL Field)
4. sum( <cond> Field)
5. sum( ALL <cond> Field)
6. sum( TOTAL <cond> Field)

All of these change in the same way upon selection, besides 5, which doesn't change at all.

The question is therefore: does 'set analysis' have precedent on the rest? And if so, is there any way to get around it?

Thanks

[marcus_sommer]

I must admit that I don't understand what your problem is - in regard with TOTAL's to calculate certain rates and the used sub-set of data.

Set Analysis worked like a selection and determines which data are available within the subset of data and like a selection it's applied before any calculation is executed.

If I look again on your origin question I assume that your struggling might be caused from rate-results within the partial-sums of the object. This is a quite common challenge if not simply expressions like sum(Field) are used else measures which conditions and/or it contained multiple calculations in combination (usually rate-calculations). The reason for it that the conditions are often not true or different on the total-level as within the lower row-level and/or heterogenous calculation-parts are leading to unexpected results. This means it happens often that the total-result isn't the sum or average of the rows. This is neither mathematically or logically wrong else more unexpected and unintended because the user expects a certain weighted result.

To make it short this kind of challenge isn't solvable with a single aggregation else it needs at least a double aggregation - an inner and an outer one. For this you could use aggr(). The logic in your case would look like:

sum(aggr(sum(Field)/sum(total Field), Dim1, Dim2))

whereby you may need the set analysis within the inner and the outer aggregation and also may the outer aggregation also need a total. Like above hinted it depends on your data, data-model and the views if you could get your results with a single expression or if you may need multiple specific ones related to the dimension-layer. This means something like:

if(dimensionality() <= 1,
   sum(aggr(sum(Field)/sum(total Field), Dim1, Dim2)),
   sum(aggr(sum(Field)/sum(total < Dim1, Dim2> Field), Dim1, Dim2, Dim3))
)

- Marcus

[lisabarelle]

My data model is dead simple - it goes like this:

STORE      COUNTRY                     SALES

A                   North America        10

B                   North America        2

C                   Europe                       25

D                   Europe                       5

E                   North America        4

My expected result is, when selecting store E (on the dashboard), to get the % sales of that store (E) relative to its region, i.e. North America. The result would look like this i.e. 4/(10+2+4) = 25%.

Here are the outputs from various trials:

sum([SALES]) /  sum(TOTAL [SALES]) = 46/46 + 100%

sum({<[COUNTRY]={'North America'}>}[SALES]) /  sum(TOTAL {<[COUNTRY]={'North America'}>}[SALES]) = 16/ 16 = 100%

(same with ALL instead of TOTAL)

Hope this clarifies a bit that I'm simply trying to use a subset of the denominator instead of the entire data set. In other words: {STORE E} E {NORTH AMERICA} E {All data}. I'm trying to compute {STORE E (user would select any store they want)} / {NORTH AMERICA (this is fixed, i.e. use cannot select a store in Europe)}.

Any other thoughts? 

[marcus_sommer · Accepted solution]

If Store E is selected you will get the following results:

sum([SALES]) /  sum(TOTAL [SALES]) = 4/4 = 100%

sum({<[COUNTRY]={'North America'}>}[SALES]) /  
sum(TOTAL {<[COUNTRY]={'North America'}>}[SALES]) = 4/ 4 = 100%

To get the rate between Store E and all North America you need to ignore this selection within the TOTAL, like: 

sum({<[COUNTRY]={'North America'}>} [SALES]) /  
sum(TOTAL {<[COUNTRY]={'North America'}, [STORE]>} [SALES]) = 4/ 16 = 25%

by just listing those fields which selections should be excluded respectively ignored.

- Marcus