Forum Discussion
combining multiple columns into one column for a table visual
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 |
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() )
1 Reply
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() )