if(dimentionality=0, sum(total [performance points])).
This statement basically ignores all dimentioned and only considers the current selection. Alternatively you can also set the method of sub total to 'Sum of Rows' in the expression tab.
It would be heloful if you could post a sample QVW, so that we all can try to help you with a solution.
I am not sure of the solution to your problem. If you dont want to post a sample qvw, at least provide the expression that gives you [performance points] and the expression for the average on the year total.
To use Dimensionality(), you can do something like this:
expression for grand (facilitiesd) total,
expression year total,
expression for lowest level
Replace the italics with the expressions for each level. Note that Dimensionality() is a function, so you need the parentheses, and you cannot use Dimensionality in a set expression, as the set expression is evaluated once for the chart/table (before the individual lines/dimensions exist). Using Pick() like above makes it easy to add or remove levels and their expressions.
The Pick(Dimensionality()+1 sounds like a good solution as long as Pivoting is not allowed.
Please have a look at my discussion below, any help appreciated.
I doubt that it is possible but I need to aggregate dynamically based on the pivot dimension PLUS an extra one. This way I could allow pivoting.
for 1st dimension in pivot - Aggr(Sum(Sales),1stDimension,ResellerID)
for 2nd dimension in pivot - Aggr(Sum(Sales),1stDimension,2ndDimension,ResellerID)
Resuming, the default pivot aggregation plus ResellerID - a dimension not in the pivot.