combining multiple columns into one column for a table visual

[dw700d]

I have a table of employees. That table shows a hierarchy of who each employee roles up to in the organization. So for example in row 1 Dan reports to his Director Tina. Tina reports to her Vice president Michael  and Michael reports to the president Jenny. See the table below.

Employee	Director	Vice President	President
Dan	Tina	Michael	Jenny
Dave		Michael	Jenny
Bob	Cecil	Thomas	Jenny
Bill Carl	Susan	Danny	tony
Steve	Susan	Danny	tony
Cheryl	Greta	Danny	tony
Sam			Jim
Dana	Candace	Gary	Paul
Joe			Paul

I am trying to create a table visual that groups the director column , vice president column and president column into one Column and calculates the total count of employees for each director, vice president and president. So for example Jenny(president) has 3 employees that roll up to her (Dan, Dave, Bob).  Michael(vice president) has 2 employees that roll up to him (Dan and Dave). below is what the visual would look like. Any thoughts on how I can accomplish this ?

Leader	Count of Employees
Jenny	3
tony	3
jim	1
paul	2
Michael	2
Thomas	1
Danny	3
Gary	1
Tina	1
Cecil	1
Susan	2
Greta	1
Candace	1

[FreemanZ · Accepted solution]

hi @dw700d 

you may try to add a calculated table like:

Table = 
FILTER(
    UNION(
        ADDCOLUMNS( VALUES(data[President]), "Count", CALCULATE(COUNTROWS(Data))),
        ADDCOLUMNS( VALUES(data[Vice President]), "Count", CALCULATE(COUNTROWS(Data))),
        ADDCOLUMNS( VALUES(data[Director]), "Count", CALCULATE(COUNTROWS(Data)))
    ),
    [President]<>BLANK()
)