The problem.

We are trying to present the average number of days it takes for
projects within a project grouping to go from one milestone to another
(advertising to notice-to-proceed).

Because there are different ProjectGroupings across the organization,
the Project Type dimension looks like this

Project Grouping
Project Group
Project Type.

The tricky part seems to be showing the average of All Projects with
the project grouping, and displaying that along side the project group
averages. This chart shows these measures by year.

For Example for each year it would show the average for Group 1, Group
2, Group 3 as well as average of All Projects Across those groups.

Using the calculated member, I seem to be able to calculate the Average
of all using the following MDX.

Sum([Proj Type Govs Rpt].[Project Grouping].AllMembers,[Measures].[Days
AD to NTP])/Sum([Proj Type Govs Rpt].[Project Grouping].AllMembers,
[Measures].[Proj Count])

However, the value dislays as another measure with values for all
members within each year.
So even though the value is the same, it dislays as many times as there
are project groups.

I attempted to display it after the last or a specific member, but
couldn't get the IIF to work.

any help would be appreciated.

Thanks

WalterC

Does the fact table contain a row for each project; and is
[Measures].[Proj Count] is a cube 'count' measure and
[Measures].[Days AD to NTP] a 'sum' measure? In that case, the average
can be defined as: [Measures].[Days
AD to NTP]/[Measures].[Proj Count]; and you can chart the averages for
sibling groups vs. for their parent grouping.


- Deepak

Deepak Puri
Microsoft MVP - SQL Server

*** Sent via Developersdex http://www.developersdex.com ***

That is how i get the average for each product groups (siblings). How
do I calc the average for All Siblings and display in same chart with
Siblings?

Thanks for your help.