Guys,

I am new to Analysis Services 2005 (and therefore MDX). I have a Fact
table with the following fields:

OrganizationID --> dimension: Organization links in here.
ProductFamilyID --> dimension: Product links in here.
Licenses --> this is a regular measure.

The Licenses field is a measure and the other two fields are Dimensions
with various attributes (Organization - Segment, Location, Name, et &
ProductFamilyID - CategoryID).

What I need to do is find the average number of licenses in each
ProductFamilyID. I have two calculated measures and I don't know which
to use:

1. Avg Licenses by Product:

Avg(Descendants([Product].[Product].CurrentMember,[Product].[Product].[Product]),
[Measures].[MSC Licenses])

2. Avg Licenses by Organization:

Avg(Descendants([Organization].[Organization].CurrentMember,[Organization].[Organization].[Organization]),
[Measures].[MSC Licenses])

Measure #1 works great when using the Organization dimension in the Row
section, but won't work with the Products dimension (values don't
change). The opposite is true for Measure #2.

What I really need is some kind of combination - one that gives me the
average license count per Product, but it ONLY uses license counts of
Organizations that actually appear in the Browse section. So if I
decided to just show Organizations from the PacWest region for example,
the averages would change because the licenses involved in computing
the averages for the Products would be less. Am I making any sense? :P
There is a good chance that I am over complicating this too.

Thanks for any ideas or thoughts!

Sincerely,

J'son

How about this modified version of Measure #1 - if it isn't what you
want, some specific data examples would help:

Avg(Existing [Product].[Product].[Product].Members,
[Measures].[MSC Licenses])


- Deepak

Deepak Puri
Microsoft MVP - SQL Server

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

Deepak,

Thanks! What ended up working for me was a calculated measure like
this:

[Measures].[MSC Licenses]/[Measures].[MSC Organization Count]

Apparently, I was over thinking it. By creating a separate Organization
Count measure and dividing the number of licenses in that space by the
count, my averages correctly flucuate depending on the dimensions
choosen.

Thanks again.. you got me thinking in the right direction. Now I want
to go from here (a direct, simple average) to a weighted average. I'll
make a another post about that.

Sincerely,

J'son


Deepak Puri wrote: