Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
ebp2022
Frequent Visitor

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

 

PBI_Question.png

1 ACCEPTED SOLUTION
DataInsights
Super User
Super User

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

 

DataInsights_0-1673726203630.png

 

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:

 

DataInsights_1-1673726320565.png

 





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




View solution in original post

10 REPLIES 10
Anonymous
Not applicable

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:

Aaron_Manhattan_3-1697134846600.png

 

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
Frequent Visitor

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.

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.





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




@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

 





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




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? 

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





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




ebp2022
Frequent Visitor

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.

 

PBI_Question_2.png

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





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




ebp2022
Frequent Visitor

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
Super User
Super User

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

 

DataInsights_0-1673726203630.png

 

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:

 

DataInsights_1-1673726320565.png

 





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.