Calculating measures based on different hierarchy levels

[rax99]

Hi All,

I need to know how to perform calculations on different levels of a hierarchy. 

Take this matrix as an example;

Say the Customer Service is Level2 of the hierarchy. (Level 1 being client)

The STDEVP.1_Duration is the Standard deviation measure on the duration column. So as per the image above I need to add a new column beside it where it takes the Duration and subtracts the STDEVP.1_duration of the parent level. So in the example above for Customer Service, Total = 22194891 - 406 = 22194485.

How do we perform calculations on different levels of the hierarchy?

[theov]

Basically we use ISFILTERED for such requirements. However, sometimes your total line might be wrong or empty, in that case you can add HASONEFILTER to detect total line and calculate it differently
Also you might need to have different number formats for each level, in that case you can add FORMAT function. Yet because it is a text function you might have empty lines, so you need to add ISBLANK.

This video explains fully the production based on hierarchy level with example 🙂

https://www.youtube.com/watch?v=LV9LqcEmgFc

[rax99 · Accepted solution]

So I managed to solve this using this DAX:

IF (
  ISFILTERED ( AgentTable[EmployeeID] ),
  CALCULATE([STDEVP.1_Duration],ALL(AgentTable[EmployeeID])) - SUM(Duration),
     IF (
        ISFILTERED ( AgentTable[TeamID] ),
        CALCULATE([STDEVP.1_Duration],ALL(AgentTable[TeamID]))- SUM(Duration),
             IF (
                 ISFILTERED ( AgentTable[DeptID] ),
                 CALCULATE([STDEVP.1_Duration],ALL(AgentTable[DeptID]))- SUM(Duration),
                     IF (
                        ISFILTERED ( AgentTable[ClientID]),
                        CALCULATE([STDEVP.1_Duration],ALL(AgentTable[ClientID]))- SUM(Duration)

        )
    )
)

[v-danhe-msft]

Hi @rax99 ,

It is hard to reporduce your data structure, could you please post a simple sample to have a test for your problem?

Regards,

Daniel He