Forum Discussion
Data Modeling for Manager-Supervisor-Employee Results Hierarchy
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:
Super UserWhat tables have a relationship with the calendar table? If you could provide the underlying rows of the 549 vs. 542 in your screenshot, as well as the 1,138 that would be helpful (an example of a supervisor with an incorrect total and a supervisor with the correct total). Also include a screenshot of the date slicer so the date range is clear.
Super User
Here's a different approach. Let me know if it resolves the issue.
Sales =
VAR vTable =
ADDCOLUMNS (
SUMMARIZE (
EmployeeHierarchy,
EmployeeHierarchy[Date],
EmployeeHierarchy[Employee ID]
),
"@Amount",
VAR vDate = EmployeeHierarchy[Date]
RETURN
CALCULATE (
SUM ( FactSales[Sales] ),
CROSSFILTER ( EmployeeHierarchy[Employee ID], DistinctEmployee[Employee ID], BOTH ),
FactSales[Date] = vDate
)
)
VAR vResult =
SUMX ( vTable, [@Amount] )
RETURN
vResult
This one works except if the employee ID is null. It happens from time to time that the datasource includes an employee that is not from this location. The results for that null employee gets assigned randomly to a supervisor/manager hierarchy for some reason. Any thoughts around that?
- DataInsights
You could exclude null Employee IDs in Power Query (assuming you don't want these in your model). Or, you could replace null with a value like -1.
