Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
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 |
Solved! Go to 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()
)
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()
)
User | Count |
---|---|
73 | |
70 | |
38 | |
24 | |
23 |
User | Count |
---|---|
96 | |
93 | |
50 | |
41 | |
40 |