Data modeling with cascaded relationship

[excelmonk]

Dear All,

Here is my simplified model. There are three tables - Fact Table, Region table and Employee table. Sample data is as follows:

Fact table


Region table



Employee table


Relationship are defined in model as

Facttable[Employee] = Employee Table [ Employee]
Employee Table [ Region] = Region table [ Region]

My requirement is to build a pivot with Region Manager name and get the Total of appointments for each Manager. I use a simple measure

TotalAppointments=CALCULATE(sum(Fact_Table[Appointmentsdone]))

Here is how the data looks with this measure for employees and managers



How do I get Manager A same as Employee A i.e. 8, manager B =16 and Manager C =8.

Thanks!

 

[AlanSidman]

Will you please attach a sample Excel workbook? We are not able to work with or manipulate a picture of one and nobody wants to have to recreate your data from scratch.

1. Make sure that your sample data are REPRESENTATIVE of your real data. The use of unrepresentative data is very frustrating and can lead to long delays in reaching a solution.

2. Make sure that your desired results are also shown (mock up the results manually).

3. Make sure that all confidential data is removed or replaced with dummy data first (e.g. names, addresses, E-mails, etc.).

4. Try to avoid using merged cells as they cause lots of problems.

Please pay particular attention to point 2 (above): without an idea of your intended outcomes, it is often very difficult to offer appropriate advice.