Visual Totals Not Updating Properly Due to Relationships Between Fact and Dimension Tables

[Cedberg]

I am in the process of creating historical/point in time performance reporting for my call center.  In the business, it is not usual for an employee to report to different supervisors throughout the year.  I have created and update weekly an ongoing list of employees and the supervisor they report to, essentially creating a fact table.

I have created a relationship with this employee table with a calendar table in order to determine who an employee reported to at any given time of the year.  The point is to be able to rollup employee data up to the supervisor and be able to track and trend  supervisor performance throughout the year.

I have been able to create the relationships to show the proper employee under the proper supervisor at any point in time but my issues start when I try to connect this employee table with additional fact tables (performance data).  I am able to show the proper performance metrics to the individual employee but I am not able to get the data to properly roll up to the supervisor level.

I know the issue is because I am trying to pull data from two fact tables without the proper dimension tables.  However, to create the dimension table with the correct employee to supervisor hierarchy, that data would need to come from the employee table which is a fact table.

Any ideas and help on how to have the data properly roll up to the supervisor level will be greatly appreciated!

Table with employee data not correctly rolling up to Supervisor:

[Cedberg · Accepted solution]

I was able to solve the issue by adding some additional information during the query process that allowed me to properly roll up the data by the correct supervisor for any point in time.

[v-piga-msft]

Hi @Cedberg,

I suggest you could change the direction highlight in red to both then try again.

If you still need help, please share a dummy pbix file which can reproduce the scenario, so that we can help further investigate on it? You can upload it to OneDrive or Dropbox and post the link here. Do mask sensitive data before uploading.)

I would appreciate it if you could share you desired output.

Best Regards,

Cherry