Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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 the Count of direct as well as indirect for each manager.
How can we do this ?
Thanks in Advance
You need help regarding hierarchy creation or to show the count for direct/indirect?
I am looking top show the count
Sorry ,its a type mistake.
We are looing for Count of employees under each manager direct and indirect reportees.
Thanks in Advance
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.
Where to enter the
CalculatedTable ?
Hi Eric,
Thank you for this interesting piece of code; however, where do you enter the code for calculatedtable?
Hi Eric,
Thank you for this interesting piece of code; however, where do you enter the code for calculatedtable?
Thanks a lot Eric, this has solved my problem. I wouldn't have been able to do it without this post; kudos.
Where does this code go?
@vsatamraju to be able to create drill down in power bi visuals you need to drop multiple fields into Axis area. provided you have columns of organisational level, manager and count of employees you can use Pivot column feature in query editor in power bi desktop to seperate out organisational level as individual columns. you can then drop those fields into Axis area and count of employees onto Value area.
The way you operate this depends on the structure of your data.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
110 | |
94 | |
81 | |
66 | |
58 |
User | Count |
---|---|
150 | |
119 | |
104 | |
87 | |
67 |