Forum Discussion

UK06B1's avatar
UK06B1
Helper II
3 years ago

Calculating Differences between employees depending on hierachy level

Hello

I have a requirement to calculate the difference between total remuneration of employees and their managers within an organisation.

 

I have a table which has a list of all employees (includes their employee ID) along with their manager (including ID). I also have a table with all employees total remuneration. 

 

I have seen a couple of posts around defining hierachies using the PATH function, but i am struggling to see how i can then use this to bring through the total remuneration to then work out the differences. 

 

As an example in the below table i would want to have a column that brings in total remuneration of the employee , then a column which is the total remuneration of the manager, they are stored in sepaarte tables.  Then add a column ( or use a calcuated measure in the main report ? ) which calculates the difference bwteen the manager and the employees alogned to them. 

 

E.g. employee 1 paid 20k their manager (employee 5) paid 30k , the calculated difference being 10k. I would have thought this would be alot easier than it seems. 

 

any help greatly appreciated, 

 

 

Need Help

4 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    Hi UK06B1 ,

     

    You can create a measure.

    IF (
  ISFILTERED ( Employee[EmployeeID] ),
  CALCULATE([STDEVP.1_Remuneration],ALL(Employee[EmployeeID])) - SUM(Remuneration),
     IF (
        ISFILTERED ( Employee[TeamID] ),
        CALCULATE([STDEVP.1_Remuneration],ALL(Employee[TeamID]))- SUM(Remuneration),
             IF (
                 ISFILTERED ( Employee[DeptID] ),
                 CALCULATE([STDEVP.1_Remuneration],ALL(Employee[DeptID]))- SUM(Remuneration),
                     IF (
                        ISFILTERED ( Employee[ClientID]),
                        CALCULATE([STDEVP.1_Remuneration],ALL(Employee[ClientID]))- SUM(Remuneration)

        )
    )
)

    You can refer to the following documents that may be helpful to you:

    matrix - How to calculate based on different levels of a hierarchy - Power BI - Stack Overflow

    www.reddit.com

    Clever Hierarchy Handling in DAX - SQLBI

    Solved: Transfers In Count in different hierachies - Microsoft Power BI Community

     

    If the above one can't help you get the desired result, please provide some sample data in your tables (exclude sensitive data) with Text format and your expected result with backend logic and special examples. It is better if you can share a simplified pbix file. Thank you.

     

    Best Regards,

    Neeko Tang

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly. 

     

     

    • UK06B1's avatar
      UK06B1
      Helper II
      3 years ago

      Thanks alot Neeko Tang i will take a look at what you ahve suggested and get back to you if i am still unable to work it out. regards. 

      • Anonymous's avatar
        Anonymous
        Not applicable
        3 years ago

        Hi UK06B1 ,

         

        Has your problem been solved? If the problem has been solved you can mark the reply for the standard answer to help the other members find it more quickly. If not, please point it out. Thanks in advance.

         

        Best Regards,

        Neeko Tang

        If this post helps, then please consider Accept it as the solution to help the other members find it more quickly. 

    • UK06B1's avatar
      UK06B1
      Helper II
      3 years ago

      Hello Neeko Tang, apologies for the delay i struggled to get the solution to achieve what i required (i think this is an issue with my capability rather than your solution). In the end i was able to achieve the result i needed by creating 2 tables and a calculated column in one of the data sets. I appreciate you help though.