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:

     

     

Anonymous's avatar
Anonymous
Not applicable
2 years ago

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.