cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Shashant Frequent Visitor
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.

 

Query-Pic.png

1 ACCEPTED SOLUTION

Accepted Solutions
Community Support Team
Community Support Team

Re: Count of direct reportee in Flattened hierarchy

Hi @Shashant

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()))))

3.png

 

 

Best Regards

Maggie

View solution in original post

5 REPLIES 5
ThomasFoster Established Member
Established Member

Re: Count of direct reportee in Flattened hierarchy

How is your original data containing users and managers structured?

Shashant Frequent Visitor
Frequent Visitor

Re: Count of direct reportee in Flattened hierarchy

It is in Employee  Manager format

Shashant Frequent Visitor
Frequent Visitor

Re: Count of direct reportee in Flattened hierarchy

Any direction on the implementation please.

Community Support Team
Community Support Team

Re: Count of direct reportee in Flattened hierarchy

Hi @Shashant

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()))))

3.png

 

 

Best Regards

Maggie

View solution in original post

Shashant Frequent Visitor
Frequent Visitor

Re: Count of direct reportee in Flattened hierarchy

Thanks Maggie,
That worked as required.

Helpful resources

Announcements
New Topics Started Badges Coming

New Topics Started Badges Coming

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

MBAS 2020

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

Difinity Conference

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

Top Solution Authors
Top Kudoed Authors (Last 30 Days)