Hi all, I'm hoping someone can answer this question for me - I have seen similar questions asked all over the web, some of which get solutions, but nothing I have seen directly helps by business problem.

We have a cube which uses a "Department" dimension with 9 levels, and a standard Date dimension. Measures include "Incident Count" which is a distinct-count aggregator.
A good example of a typical output would be a line graph visualization which shows over a 12 month period, for a given Level 3 Department, how many incidents are occurring month-by-month. This is all working happily.

We want to introduce the concept of "Incident Targets" - so that for each Department we can capture how many Incidents we would like to have, along with the already existing data we have about how many Incidents actually occurred. This would allow us to plot the actuals versus the targets on a line graph etc.
Targets would typically be captured and reported on per-month

The trick is that the targets should not be aggregated on the Department dimension.
If Department A is the parent of Departments X, Y and Z... and Departments X, Y and Z all have targets of 10... it is not guaranteed that the target of Department A is 30. It just doesn't work like that on the business side - different KPIs and management philosophies at different levels of the business etc.

I guess that aggregating over time does make sense... a monthly target of 10 over a year would give an annual target of 120.

So assuming my data captures a Target for every Department/Month combination... is there a way to define the Target measure (or dimension?) or refer to it in MDX so that it will not get aggregated - it will just return the single value stored... while any other measures in the query continue to be aggregated as normal? Ideally I am looking for a solution that would work in Analysis Reports - where the user can run the reports at any Department level. However, I'll take ANY solution!!

Any ideas?