Forum Discussion

UK06B1's avatar
UK06B1
Icon for Helper II rankHelper 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 (includ...
Need Help
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
    Icon for Helper II rankHelper 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
    Icon for Helper II rankHelper 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.