Data Modeling for Manager-Supervisor-Employee Results Hierarchy

[ebp2022]

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 ranges (could be weekly, daily, or running month to date - basically should work in any date range selection).

I am able to accomplish this today but with a lot of merging and appending. With the hundreds of thousands of rows I have it's becoming a performance nightmare for my approach. I would appreciate any insights you can provide. Thanks in advance.

[Anonymous]

Hi there, I have a similar issue. I'm kind of new to Power BI though and am trying to figure out if it would be a good tool to use/how to use it. I've included my data and what I want it to look like below:

Basically, I currently have the number of sales made per person (Table 1) and then I have a separate table for employees and who that employee reports to (Table 2).

I used some vlookup formulas to add in who the employees uplines are (Table 3). And then I manually shifted them over and came up with arbirtrary titles (Table 4) so that I could pivot. However, I'm unhappy with this approach because
1) it required manual manipulation and I don't know if there's a quicker way to get to the display I want, and
2) my pivot table has a bunch of blanks in it—that is, it's not able to handle the variable length sales hierarchies I have.

Ultimately, I just want a display that I can show my boss where he can see the top employee's sales and then drill down to see the sales for people underneath him.

[ebp2022]

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]

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]

@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]

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]

@ebp2022,

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.

[ebp2022]

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]

@ebp2022,

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.

[ebp2022]

Wow! Amazing! It actually worked! Thank you!

I put the measure side by side with the old measure and they match up to 2-3 days of data. As I expand the date range, however, while the overall total is the same, the manager totals don't match. I'll investigate further but would you have any theories why this is the case? 

[DataInsights · Accepted solution]

@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: