Count of direct reportee in Flattened hierarchy

[Anonymous]

Hi,

Seeking your help to solve the below problem.

Requirment is simple, in a drill down I want to display the count of people who are directly reporting into me. These levels will be displayed in the Matrix visualization and should stay unfiltered even after being drilldown to the last level.

[v-juanli-msft · Accepted solution]

Hi @Anonymous

Create measures

count_level1 = CALCULATE(DISTINCTCOUNT(Sheet2[level2]),ALLEXCEPT(Sheet2,Sheet2[level1]))

count_level2 = CALCULATE(DISTINCTCOUNT(Sheet2[level3]),ALLEXCEPT(Sheet2,Sheet2[level1],Sheet2[level2]))

count_level3 = IF(ISBLANK(MAX([level4])),0,CALCULATE(DISTINCTCOUNT(Sheet2[level4]),ALLEXCEPT(Sheet2,Sheet2[level1],Sheet2[level2],Sheet2[level3])))

Measure = IF(HASONEFILTER(Sheet2[level4]),0,IF(HASONEFILTER(Sheet2[level3]),[count_level3],IF(HASONEFILTER(Sheet2[level2]),[count_level2],IF(HASONEFILTER(Sheet2[level1]),[count_level1],BLANK()))))

Best Regards

Maggie

[Anonymous]

Thanks Maggie,
That worked as required.

[Anonymous]

How is your original data containing users and managers structured?

[Anonymous]

It is in Employee  Manager format

[Anonymous]

Any direction on the implementation please.