cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Microsoft vsatamraju
Microsoft

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 the Count of direct as well as indirect for each manager.

 

How can we do this ?

 

Thanks in Advance

11 REPLIES 11
ankitpatira Super Contributor
Super Contributor

Re: Creating a Hierarchy in Power BI

@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.

Habib Established Member
Established Member

Re: Creating a Hierarchy in Power BI

You need help regarding hierarchy creation or to show the count for direct/indirect?

Microsoft vsatamraju
Microsoft

Re: Creating a Hierarchy in Power BI

I am looking top show the count 

Microsoft vsatamraju
Microsoft

Re: Creating a Hierarchy in Power BI

Sorry ,its a type mistake.

 

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

 

Thanks in Advance

Highlighted
Moderator Eric_Zhang
Moderator

Re: Creating a Hierarchy in Power BI

@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.


Capture.PNG

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.

Microsoft vsatamraju
Microsoft

Re: Creating a Hierarchy in Power BI

@Eric_Zhang ,

 

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

efglynn Regular Visitor
Regular Visitor

Re: Creating a Hierarchy in Power BI

Where does this code go?

remig Frequent Visitor
Frequent Visitor

Re: Creating a Hierarchy in Power BI

@Eric_Zhang

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

rebin40 Frequent Visitor
Frequent Visitor

Re: Creating a Hierarchy in Power BI

Hi Eric, 

 

Thank you for this interesting piece of code; however, where do you enter the code for calculatedtable? 

Helpful resources

Announcements
New Topics Started Badges Coming

New Topics Started Badges Coming

We're releasing new versions of the badge that everyone's talking about. ;) Check your inbox for notifications.

MBAS 2020

Save the new date (and location)!

Our business applications community is growing—so we needed a different venue, resulting in a new date and location. See you there!

Difinity Conference

Difinity Conference

The largest Power BI, Power Platform, and Data conference in New Zealand

Power Platform 2019 release wave 2 plan

Power Platform 2019 release wave 2 plan

Features releasing from October 2019 through March 2020

Top Solution Authors
Top Kudoed Authors (Last 30 Days)