Forum Discussion
Solved
Data Modeling for Manager-Supervisor-Employee Results Hierarchy
Good day! Below is an illustration of my tables and the desired result. Goal: To see performance results by Manager, Supervisor, Employee dynamically in different measures and different date ran...
Try this solution.
Create a table of distinct Employee ID in either Power Query or DAX. This is a bridge table between EmployeeHierarchy and each fact table.
Create measures:
Sales = VAR vDate = TREATAS ( VALUES ( EmployeeHierarchy[Date] ), FactSales[Date] ) VAR vResult = CALCULATE ( SUM ( FactSales[Sales] ), CROSSFILTER ( EmployeeHierarchy[Employee ID], DistinctEmployee[Employee ID], BOTH ), vDate ) RETURN vResultComplaints = VAR vDate = TREATAS ( VALUES ( EmployeeHierarchy[Date] ), FactComplaint[Date] ) VAR vResult = CALCULATE ( SUM ( FactComplaint[Complaints] ), CROSSFILTER ( EmployeeHierarchy[Employee ID], DistinctEmployee[Employee ID], BOTH ), vDate ) RETURN vResultSatisfaction Score = VAR vDate = TREATAS ( VALUES ( EmployeeHierarchy[Date] ), FactSatisfactionScore[Date] ) VAR vResult = CALCULATE ( MAX ( FactSatisfactionScore[Satisfaction Score] ), CROSSFILTER ( EmployeeHierarchy[Employee ID], DistinctEmployee[Employee ID], BOTH ), vDate ) RETURN vResultAdd EmployeeHierarchy fields and the measures to a visual:
Here's a screenshot. As you can see the individual employee name totals are correct but the supervisor totals are not and therefore leading to incorrect manager totals.
- DataInsights
Super User
It looks like an issue with filter context at the supervisor and manager levels. DAX totals are actually queries that sometimes don't equal the sum of the parts. Would you be able to provide an example pbix with sanitized data? You can use one of the file services like OneDrive.