Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

Calculating measures based on different hierarchy levels

Hi All,   I need to know how to perform calculations on different levels of a hierarchy.    Take this matrix as an example;                                   Say the Custo...
Need Help
Show and Tell
Tips and Tricks
Tutorial Requests
  • Anonymous's avatar
    Anonymous
    7 years ago

    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)

        )
    )
)
theov's avatar
theov
Icon for Advocate III rankAdvocate III
4 years ago

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