Forum Discussion

ebp2022's avatar
ebp2022
Frequent Visitor
3 years ago
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...
Need Help
Tips and Tricks
  • DataInsights's avatar
    DataInsights
    3 years ago

    ebp2022,

     

    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
    vResult
    Complaints = 
VAR vDate =
    TREATAS ( VALUES ( EmployeeHierarchy[Date] ), FactComplaint[Date] )
VAR vResult =
    CALCULATE (
        SUM ( FactComplaint[Complaints] ),
        CROSSFILTER ( EmployeeHierarchy[Employee ID], DistinctEmployee[Employee ID], BOTH ),
        vDate
    )
RETURN
    vResult
    Satisfaction 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
    vResult

     

    Add EmployeeHierarchy fields and the measures to a visual:

     

     

ebp2022's avatar
ebp2022
Frequent Visitor
3 years ago

I appreciate the response. I'm trying to sanitize the data but this is a huge model and contains a lot of personal information I'm not even sure how to redact and still keep the integrity of the data and relationships.

 

I'm doing some more digging and here's the current situation:

1. Supervisor levels are correct if it's a single date

2. Grand total is always correct

3.  Majority of the supervisors have correct subtotals, there would be 1 or 2 with incorrect subtotals of about 30-40 supervisors displayed at a time

 

I'm also doing some additional research, maybe you can let me know which areas to further look at? Also, I don't know if it matters but I have a centralized calendar table which I use for visual filtering.

  • DataInsights's avatar
    DataInsights
    Icon for Super User rankSuper User
    3 years ago

    What 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.

    • DataInsights's avatar
      DataInsights
      Icon for Super User rankSuper User
      3 years ago

      ebp2022,

       

      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

       

      • ebp2022's avatar
        ebp2022
        Frequent Visitor
        3 years ago

        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?