I need help in defining a calculated measure.
I have a dimension table that contains a list of clients.
The clients themselveves are in a parent-child hierarchy
that represents several levels of subsiduaries and outlets.
Every record in the table also contains a column where
is listed the responsible employee sales rep for the given
client at which ever level of the hierarchy the current client
may find itself.
This employee column, is the basis for an employee dimension.
Our fact table contains sales per client.
Now, when I look at the sales per employee, I do NOT want to see
just the sales where he/she is directly responsble for, I want to
see the total sales summed for all clients at and below his own in the
client hierarchy including the values for each of those clients.
How do I define the calculated member to do this?
Here are the tables (simplified of course):
client_key (used in parent-child client dimension)
client_name (used in parent-child client dimension)
responsible_employee_key (used in employee dimnsion)
responsible_employee_name (used in employee dimnsion)
actual_budget_key (additional acutal budget dimension)
I'd bevery thankful for help on how to best implement the calculated