Ragged hierarchy for organization structure nr of employees count

[Guido_Beulen]

Hi fellow BI users,

I have this question: I have used the path function to get the ID's for employees and their managers across levels - the data source only shows each employee and their direct manager. Now I get all levels: 

Path = PATH('Dim Medewerker'[res_id],'Dim Medewerker'[repto_id])

Then I established the 8 levels necessary within our organization by adding custom columns: 

(for example): level 2 = PATHITEM([Path],2)

I then added the names in custom columns: 

Leidinggevende niveau 1 = LOOKUPVALUE('Dim Medewerker'[fullname],'Dim Medewerker'[res_id],'Dim Medewerker'[level 1])

Then I created measures that remove those annoying blanks from the results: they calculate each unique value in that column in a measure.

(1)

#employees level 2 = CALCULATE(DISTINCTCOUNT('Dim Medewerker'[Leidinggevende niveau 2]),'Dim Medewerker'[Leidinggevende niveau 2] <> BLANK())

Now here's the tricky part: 

I want to calculate the total number of employees under a given manager, not just the ones directly below but also the ones below their direct and indirect hierarchical responsibility. Obviously, in my Hierarchy (I am using a hierarchy filter) I am selecting a value from any hierarchy column, could be level1, could be 3, could be 4. 

So my measure should count sum up the results from measures like (1), depending on the level my selectedvalue was in. So if I select a level 4 manager, it should look for the SUM of #employees level 5, #employees level 6, #employees level 7, #employees level 8/. For a level 2 manager it should SUM level 3, 4, 5, 6, 7, 8. 

Below is the kind of hierarchy slicer I use: 

 https://www.youtube.com/watch?v=Q-fT10OI6uI

[Guido_Beulen]

Ok, what I came up with is this: 

Aan hoeveel mensen (in)direct leiding gegeven wordt =

IF (

ISFILTERED ('Dim Medewerker'[Leidinggevende niveau 1]

),

[Aantal medewerkers leidingevende 2]+[Aantal medewerkers leidingevende 3]+[Aantal medewerkers leidingevende 4]+[Aantal medewerkers leidingevende 5]+[Aantal medewerkers leidingevende 6]+[Aantal medewerkers leidingevende 7]+[Aantal medewerkers leidingevende 8],

IF (

ISFILTERED ('Dim Medewerker'[Leidinggevende niveau 2]

),

[Aantal medewerkers leidingevende 3]+[Aantal medewerkers leidingevende 4]+[Aantal medewerkers leidingevende 5]+ [Aantal medewerkers leidingevende 6]+[Aantal medewerkers leidingevende 7]+[Aantal medewerkers leidingevende 8],

IF (

ISFILTERED ('Dim Medewerker'[Leidinggevende niveau 3]

),

[Aantal medewerkers leidingevende 4]+[Aantal medewerkers leidingevende 5]+[Aantal medewerkers leidingevende 6]+[Aantal medewerkers leidingevende 7]+[Aantal medewerkers leidingevende 8],

IF (

ISFILTERED ('Dim Medewerker'[Leidinggevende niveau 4]

),

[Aantal medewerkers leidingevende 5]+[Aantal medewerkers leidingevende 6]+[Aantal medewerkers leidingevende 7]+[Aantal medewerkers leidingevende 8],

IF (

ISFILTERED ('Dim Medewerker'[Leidinggevende niveau 5]

),

[Aantal medewerkers leidingevende 6]+[Aantal medewerkers leidingevende 7]+[Aantal medewerkers leidingevende 8],

IF (

ISFILTERED ('Dim Medewerker'[Leidinggevende niveau 6]

),

[Aantal medewerkers leidingevende 7]+[Aantal medewerkers leidingevende 8],

IF (

ISFILTERED ('Dim Medewerker'[Leidinggevende niveau 7]

),

[Aantal medewerkers leidingevende 8],"None")))))))

It does, however, suffer from the 'empty values' in the calculation due to null values in the ragged hierarchy. Does anybody know a way to fix that? I've seen some stuff on Guy In A Cube and something about ISINSCOPE, but that seems to be a pretty crafty (meaning: lots of work by hand) solution and I am not even sure it will solve the calculation issue...

[Guido_Beulen · Accepted solution]

I fixed it using the following; 

Measure 1:

Filterniveau hierarchy =

SWITCH(TRUE,

ISFILTERED('Dim Medewerker'[Leidinggevende niveau 9]), "level 9",

ISFILTERED('Dim Medewerker'[Leidinggevende niveau 8]), "level 8",

ISFILTERED('Dim Medewerker'[Leidinggevende niveau 7]), "level 7",

ISFILTERED('Dim Medewerker'[Leidinggevende niveau 6]), "level 6",

ISFILTERED('Dim Medewerker'[Leidinggevende niveau 5]), "level 5",

ISFILTERED('Dim Medewerker'[Leidinggevende niveau 4]), "level 4",

ISFILTERED('Dim Medewerker'[Leidinggevende niveau 3]), "level 3",

ISFILTERED('Dim Medewerker'[Leidinggevende niveau 2]), "level 2",

ISFILTERED('Dim Medewerker'[Leidinggevende niveau 1]), "level 1",

"niet gefilterd"

)

followed by measure 2: 

Aan hoeveel mensen (in)direct leiding gegeven wordt =

IF (

[Filterniveau hierarchy] = "level 1",

[Aantal medewerkers leidingevende 2]+[Aantal medewerkers leidingevende 3]+[Aantal medewerkers leidingevende 4]+[Aantal medewerkers leidingevende 5]+[Aantal medewerkers leidingevende 6]+[Aantal medewerkers leidingevende 7]+[Aantal medewerkers leidingevende 8]+[Aantal medewerkers leidingevende 9],

    IF ([Filterniveau hierarchy] = "level 2",

    [Aantal medewerkers leidingevende 3]+[Aantal medewerkers leidingevende 4]+[Aantal medewerkers leidingevende 5]+[Aantal medewerkers leidingevende 6]+[Aantal medewerkers leidingevende 7]+[Aantal medewerkers leidingevende 8]+[Aantal medewerkers leidingevende 9],

IF ([Filterniveau hierarchy] = "level 3",

[Aantal medewerkers leidingevende 4]+[Aantal medewerkers leidingevende 5]+[Aantal medewerkers leidingevende 6]+[Aantal medewerkers leidingevende 7]+[Aantal medewerkers leidingevende 8]+[Aantal medewerkers leidingevende 9],

IF ([Filterniveau hierarchy] = "level 4",

[Aantal medewerkers leidingevende 5]+[Aantal medewerkers leidingevende 6]+[Aantal medewerkers leidingevende 7]+[Aantal medewerkers leidingevende 8]+[Aantal medewerkers leidingevende 9],

IF ([Filterniveau hierarchy] = "level 5",

[Aantal medewerkers leidingevende 6]+[Aantal medewerkers leidingevende 7]+[Aantal medewerkers leidingevende 8]+[Aantal medewerkers leidingevende 9],

IF ([Filterniveau hierarchy] = "level 6",

[Aantal medewerkers leidingevende 7]+[Aantal medewerkers leidingevende 8]+[Aantal medewerkers leidingevende 9],

IF ([Filterniveau hierarchy] = "level 7",

[Aantal medewerkers leidingevende 8]+[Aantal medewerkers leidingevende 9],

IF ([Filterniveau hierarchy] = "level 8",

[Aantal medewerkers leidingevende 9],

"Please select"))))))))

This only uses the measures for each level (and adds them together) for a selected hierarchy level that are above the level selected (in terms of hierarchy within the organisation: below the level that is selected - so people that are subjected in terms of hierarchy). 

[v-cgao-msft]

Hi @Guido_Beulen ,

Please check if these are helpful.

Parent-child hierarchies

Dealing with Blanks Ragged Hierarchies in PowerBI (ISINSCOPE)

Or provide some sample files for testing that do not contain sensitive data? This would be very helpful, thanks in advance!

How to provide sample data in the Power BI Forum

Best Regards,
Gao
Community Support Team

If there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly -- How to provide sample data

[Guido_Beulen]

Cheers for the reply Gao! I need to do some more reading on ISINSCOPE, because I do feel it is a useful function but in my example it tried several things with it but could not get it to work in the way I was looking for.