Below is the screenshot of my pivot table for 1 facility:
I was able to customize my partial sum of dimension "Year" to calculate the AVERAGE of the points for each facility for each year (i.e. the "Points" row). At the last row, I would like to do the SUM of 'Points' row of ALL hospitals to get the total score of all the facilities in the system. I am aware that I need to use the dimensionality() function and sum advanced aggregation function that I used to get the average; however, my expression does not seem to work, what I tried is something like:
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.
Logic will get you from a to b. Imagination will take you everywhere. - A Einstein