Calculating Differences between employees depending on hierachy level

[UK06B1]

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, 

[Anonymous]

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]

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. 

[UK06B1]

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]

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.