Forum Discussion

vsatamraju's avatar
vsatamraju
Microsoft Employee
10 years ago

Creating a Hierarchy in Power BI

Hi All,   We are Clustered Column Chart in Power BI. We are implementing the drill down feature for employee heirarchy from top to bottom according to the Organization depth. Now we want to show t...
vsatamraju's avatar
vsatamraju
Microsoft Employee
10 years ago

I am looking top show the count 

vsatamraju's avatar
vsatamraju
Microsoft Employee
10 years ago

Sorry ,its a type mistake.

 

We are looing for Count of employees under each manager direct and indirect reportees.

 

Thanks in Advance

  • Eric_Zhang's avatar
    Eric_Zhang
    Microsoft Employee
    10 years ago

    vsatamraju

     

    Suppose the dataset is as the 3 columns highlighted. Create a calculated column [path] and table [calculatedTable] as below. Then use a measure subordinates in the chart.


    path = PATH(Employees[Name],Employees[Parent])

CalculatedTable =
FILTER (
    CROSSJOIN (
        FILTER (
            ALL ( Employees[Name], Employees[Title] ),
            Employees[Title] = "manager"
        ),
        ALL ( Employees[path] )
    ),
    PATHCONTAINS ( Employees[path], Employees[Name] )
)

    subordinates = COUNTA(CalculatedTable[path])-1

    You can check more details in the attached pbix. Also check Understanding Functions for Parent-Child Hierarchies in DAX.

     

    If it solves your problem, please accept it as solution. For any question, feel free to let me know.

    • vsatamraju's avatar
      vsatamraju
      Microsoft Employee
      10 years ago

      Eric_Zhang ,

       

      I havent start this, i will let you know once i started.

    • efglynn's avatar
      efglynn
      Advocate IV
      9 years ago

      Where does this code go?

    • remig's avatar
      remig
      Frequent Visitor
      9 years ago

      Eric_Zhang

      Thanks a lot Eric, this has solved my problem. I wouldn't have been able to do it without this post; kudos.