There is a "Employee" dimension that is built using the parent-child relationship on the source table columns named “employee_id” and “manager_id”. There is a need for a measure "Total People" whose value should be the count of the people working under a Manager. Obviously, the scope of this measure would be only along the “Employee” dimension, that is, the measure value changes only when the user drills along the “Employee” dimension. Since the Analysis Manager’s Cube Editor UI only allows measures to be selected from amonst the fact table columns, how can such a measure be created?

Create a calculated member using this formula:

Descendants(Employees.CurrentMember).Count - 1

The -1 is because (oddly) MDX includes the member in the set.

public @ the domain below
www.tomchester.net


"Ravindra Babburi" <rbbabburi (AT) yahoo (DOT) com> wrote

"manager_id". There is a need for a measure "Total People" whose value
should be the count of the people working under a Manager. Obviously, the
scope of this measure would be only along the "Employee" dimension, that is,
the measure value changes only when the user drills along the "Employee"
dimension. Since the Analysis Manager's Cube Editor UI only allows measures
to be selected from amonst the fact table columns, how can such a measure be
created?

Using the formula "Descendants(Employees.CurrentMember).Count - 1" is giving a count where the summary members are also getting counted.
For example, in a scenario like thi
Employee
Employee
Employee
Employee
Employee
Employee
Employee
Employee
Employee
The count for Employee2 and Employee3 is correctly shown as 3 but for Employee1 the count is 10 instead of 8 since the summary rows for Employee2 and Employee3 are contributing a 2 to the count.
How can I avoid the counting of the summary members?

Here you go:

Descendants(Employees.CurrentMember,,LEAVES).Count

public @ the domain below
www.tomchester.net


"Ravindra Babburi" <rbbabburi (AT) yahoo (DOT) com> wrote

and Employee3 are contributing a 2 to the count.

Thanks Tom. That works.