cancel
Showing results for
Did you mean:
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

#### New Topics Started Badges Coming

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

#### 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

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

Top Solution Authors
Top Kudoed Authors (Last 30 Days)