cancel
Showing results for
Did you mean:
Highlighted
Frequent Visitor

## Count of direct reportee in Flattened hierarchy

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.

1 ACCEPTED SOLUTION

Accepted Solutions
Community Support Team

## Re: Count of direct reportee in Flattened hierarchy

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

5 REPLIES 5
Established Member

## Re: Count of direct reportee in Flattened hierarchy

How is your original data containing users and managers structured?

Frequent Visitor

## Re: Count of direct reportee in Flattened hierarchy

It is in Employee  Manager format

Frequent Visitor

## Re: Count of direct reportee in Flattened hierarchy

Any direction on the implementation please.

Community Support Team

## Re: Count of direct reportee in Flattened hierarchy

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

Frequent Visitor

## Re: Count of direct reportee in Flattened hierarchy

Thanks Maggie,
That worked as required.

Announcements