Set analysis - ignore current row in pivot

Question

Consider that I have a table of salesman and I would like to know the average value if we do not count the current salesman:

|Salesman|sum(sales)| avg[other](sales)|
|-------------------|------------------|
|A       |  100     |          50      |
|B       |  50      |          66.6    |
|C       |  50      |          66.6    |
|D       |  50      |          66.6    |
----------------------------------------

Is that possible easily with set analysis? My real case is a bit more complicate, I will go through the aggregate function, but I dont know how to limit the set analysis to ignore the current row in pivot and take all the other rows for the current format.

In reality there are three dimensions for which the result is delimited and I would like to get the average over the two dimension, but with the third dimension being other than the current.

E.g. imagine the dimensions are Sales_City, Sales_branch and Salesman, then I want for each combination of Sales_City, Sales_brach and Salesman to get the average of Sales in the given Sales_City and Sales_Branch but over all Salesman other than the Salesman from the current row.

I hope it is atleast a bit understandable what I want to achieve.

Thank in advance!

Answer 1

Think that this can be solved without set analysis. The calculation below will give you the same result

( sum(total Sales) - sum(Sales) ) / ( count(total Salesman) - 1 )

And this is the result