how to get direct and indirect employees count

[PrachiD]

Above fig represent the hierarchy of my data.

This is my data and i want to calculate column to get who all are direct reportee and who all are my indirect reportee.
Example:-
for top number 1:- only 2 and 3 should be direct reportee for 1 and rest all should be indirect reportee (that are 4,5,6,7,8,9,10,11) for 1.(as show in fig).
for level 2:- I should get only 4,5 and 6 as direct reportee for 2 and remaining one that is 11 as indirect reportee for 2 only.
for level 3:- I should get only 7,8,9 and 10 as direct reportee for 3.
So please help me to solve this query.

[v-shex-msft]

Hi @PrachiD,

You can try to use below measure to achieve your requirement.

Direct = 
VAR direct =
    CALCULATETABLE (
        VALUES ( Sheet5[Emp ID] ),
        FILTER ( ALLSELECTED ( Sheet5 ), [Manger ID] = MAX ( [Emp ID] ) )
    )
RETURN
    IF ( ISEMPTY ( direct ) = FALSE (), CONCATENATEX ( direct, [Emp ID], "," ) )


Indirect =
VAR direct =
    CALCULATETABLE (
        VALUES ( Sheet5[Emp ID] ),
        FILTER ( ALLSELECTED ( Sheet5 ), [Manger ID] = MAX ( [Emp ID] ) )
    )
VAR indirect =
    CALCULATETABLE (
        VALUES ( Sheet5[Emp ID] ),
        FILTER ( ALLSELECTED ( Sheet5 ), [Manger ID] IN direct )
    )
RETURN
    IF ( ISEMPTY ( indirect ) = FALSE (), CONCATENATEX ( indirect, [Emp ID], "," ) )

Regards,

Xiaoxin Sheng

[PrachiD]

I tired your query.
The problem is when we are calculating Indirect for Emp ID : 1 then in indirect list I should get 4,5,6,7,8,9,10,11 also

I shoud get the result as above

[v-shex-msft · Accepted solution]

HI @PrachiD,

My formula only drill down one level, if you want to expand more levels, you can add the filter to get its child employees.

Indirect =
VAR direct =
    CALCULATETABLE (
        VALUES ( Sheet5[Emp ID] ),
        FILTER ( ALLSELECTED ( Sheet5 ), [Manger ID] = MAX ( [Emp ID] ) )
    )
VAR indirect =
    CALCULATETABLE (
        VALUES ( Sheet5[Emp ID] ),
        FILTER ( ALLSELECTED ( Sheet5 ), [Manger ID] IN direct )
    )
VAR child =
    CALCULATETABLE (
        VALUES ( Sheet5[Emp ID] ),
        FILTER ( ALLSELECTED ( Sheet5 ), [Manger ID] IN indirect )
    )
RETURN
    IF (
        ISEMPTY ( indirect ) = FALSE (),
        CONCATENATEX ( UNION ( indirect, child ), [Emp ID], "," )
    )

Notice: I haven't found a way to auto analysis all levels.

Regards,

Xiaoxin Sheng

[PrachiD]

As it is a measure I cannot take count of Direct and Indirect so is it possible to get the count of the Direct and Indirect

[Zubair_Muhammad]

@PrachiD

For Direct Count, try this MEASURE

Direct Count =
CALCULATE (
    DISTINCTCOUNT ( TableName[Emp ID] ),
    ALLEXCEPT ( TableName, TableName[Manager ID] )
)

[Zubair_Muhammad]

@PrachiD

For Indirect Count, try this MEASURE

We will have to manually add levels when they increase in this Formula

Indirect Count =
VAR level1 =
    INTERSECT ( ALL ( TableName[Manager ID] ), VALUES ( TableName[Emp ID] ) )
VAR level2 =
    INTERSECT (
        ALL ( TableName[Manager ID] ),
        CALCULATETABLE ( VALUES ( TableName[Emp ID] ), level1 )
    )
RETURN
    CALCULATE ( DISTINCTCOUNT ( TableName[Emp ID] ), UNION ( level1, level2 ) )

[PrachiD]

Thank You All  

[PrachiD]

Is it posible to get the count instead of getting the id??

[PrachiD]

I will have to find automated solution only because the levels are going to increase