Forum Discussion
Employee Report filtered by Manager
I have an employee table where along with few information about the employee, I also have their manager information.
| EmployeeName | EmployeeID | ManagerHierarchy | FTE | Department | Country |
| Stva | 3651 | _3651_34640_19989 | 1 | T | Sweden |
| Evka | 24243 | _24243_886_19989 | 0.75 | T | Sweden |
| Siva | 32136 | _32136_886_19989 | 1 | T | Sweden |
| Alva | 18509 | _18509_34640_19989 | 0.75 | T | Sweden |
| Jaik | 13390 | _13390_20809_19989 | 0.75 | T | Sweden |
| Ever | 20242 | _20242_20809_19989 | 0.75 | T | Sweden |
| Maat | 42163 | _42163_19989 | 1 | A | Sweden |
| Datz | 36057 | _36057_20809_19989 | 0.5 | T | Sweden |
| Juon | 31146 | _31146_20809_19989 | 1 | T | Sweden |
| Maov | 14773 | _14773_34640_19989 | 1 | T | Sweden |
| Ivev | 10512 | _10512_34640_19989 | 1 | S | Sweden |
| Viey | 15423 | _15423_19989 | 1 | A | Sweden |
| Kaov | 5673 | _5673_34640_19989 | 0.25 | S | Sweden |
| Clte | 43563 | _43563_19989 | 1 | A | Great Britain |
| Arvs | 19989 | _19989 | 1 | A | Great Britain |
| Tsov | 34640 | _34640_19989 | 1 | S | Great Britain |
| Stov | 5072 | _5072_34640_19989 | 1 | S | Great Britain |
| Ench | 20809 | _20809_19989 | 1 | A | Great Britain |
| Tilf | 886 | _886_19989 | 1 | A | Italy |
I want to give user the choice of manager and the user should be able to see aggregated data about the employee e.g. Total FTE for that team and line by line information.
For example, Mr. Tilf has 2 employees reporting to him: Evka and Siva, so selecting Mr Tilf will give me total FTE = 2.75
Mr Tilf 1
Mr Evka 0.75
Mr Siva 1
Can anyone help me please ?
7 Replies
- olgad
Resident Rockstar
Hi Saniat,
here is the result:First make changes in power query:
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("jZNPS8QwEMW/ivS8SP626XHVIqvsqYuXUkrQiGFLF7qxsn56M5MitKlrDyWPJD/ezJumqpLSDTrZJDyV1C8NrA0XqSANzXOV+z3YP/iv/DJvpkvqTZUUwxEgJpjgQKFolEp/IXKbyZgrbTBjlKfoBmLCLZhtW4SokgRuNChmNS7bPWl7BJLznCAJomFEef46WQymhwaJ7wwbBLGK3Gvt/I5gNMVoUEza206BB+2+cQBEZmECXkRWS919njoAKRUhTBAzcCHOvT4NcCKyDAtE8f/Id4NBjEiKiaBYwMop9mLNBU6kYMENxLU4nkN5Mg3VwRrNmsnY6L51BnLncswdRGT02Bvtbu5667Qd/65+OMOF8eIK4nDGCrEozH0xg5grXeiMZBggrKvZonv9wP9RhTcQT/mPWm377g/8CwNq/tCA2TndXpK6/gE=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [EmployeeName = _t, EmployeeID = _t, ManagerHierarchy = _t, FTE = _t, Department = _t, Country = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"EmployeeName", type text}, {"EmployeeID", Int64.Type}, {"ManagerHierarchy", type text}, {"FTE", type number}, {"Department", type text}, {"Country", type text}}),
#"Replaced Value" = Table.ReplaceValue(#"Changed Type","_","|",Replacer.ReplaceText,{"ManagerHierarchy"}),
#"Split Column by Delimiter" = Table.SplitColumn(#"Replaced Value", "ManagerHierarchy", Splitter.SplitTextByEachDelimiter({"|"}, QuoteStyle.Csv, false), {"ManagerHierarchy.1", "ManagerHierarchy.2"}),
#"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"ManagerHierarchy.1", type text}, {"ManagerHierarchy.2", type text}}),
#"Removed Columns" = Table.RemoveColumns(#"Changed Type1",{"ManagerHierarchy.1"}),
#"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"ManagerHierarchy.2", "ManagerHierarchy"}}),
#"Duplicated Column" = Table.DuplicateColumn(#"Renamed Columns", "ManagerHierarchy", "ManagerHierarchy - Copy"),
#"Split Column by Delimiter1" = Table.SplitColumn(#"Duplicated Column", "ManagerHierarchy", Splitter.SplitTextByDelimiter("|", QuoteStyle.Csv), {"ManagerHierarchy.1", "ManagerHierarchy.2", "ManagerHierarchy.3"}),
#"Changed Type2" = Table.TransformColumnTypes(#"Split Column by Delimiter1",{{"ManagerHierarchy.1", Int64.Type}, {"ManagerHierarchy.2", Int64.Type}, {"ManagerHierarchy.3", Int64.Type}}),
#"Removed Columns1" = Table.RemoveColumns(#"Changed Type2",{"ManagerHierarchy.1"}),
#"Renamed Columns1" = Table.RenameColumns(#"Removed Columns1",{{"ManagerHierarchy.2", "ParentEmployeeID"}}),
#"Removed Columns2" = Table.RemoveColumns(#"Renamed Columns1",{"ManagerHierarchy.3"}),
#"Renamed Columns2" = Table.RenameColumns(#"Removed Columns2",{{"ManagerHierarchy - Copy", "ManagerHierarchy"}})
in
#"Renamed Columns2"
from left to rightThen ölets create 3 calculated columns.
Level = PATHLENGTH(emloyee[ManagerHierarchy])ManagerName = LOOKUPVALUE(emloyee[EmployeeName],emloyee[EmployeeID],emloyee[ParentEmployeeID])Team = IF(emloyee[Level]>2,LOOKUPVALUE(emloyee[EmployeeName],emloyee[EmployeeID],PATHITEM(emloyee[ManagerHierarchy],2,INTEGER)),emloyee[EmployeeName])Let me know if you succeeded.
- olgad
Innovative, give a kudo!:) What do you mean you cannot use the table, it is the data you provided, you said you have such a table. Where are you gonna take the info from then? Try to reformulate, i do lots of HR reports, may be i can help further.
Wasnt this the result you needed?
Thanks olgad !
I will be reporting the figure 2.75 in many places, cannot really put the whole list everytime.
CNENFRNLCommunity Champion
just keep the dataset as it is,
Hi Anonymous
You can create a new table to filter the manager:
Manager = var _countrows=ADDCOLUMNS('employee',"counts",COUNTROWS(FILTER('employee',CONTAINSSTRING([ManagerHierarchy],EARLIER('employee'[EmployeeID]))))) return SUMMARIZE(FILTER(_countrows,[counts]>1),[EmployeeName],[EmployeeID])Then put the Employee column in Manager table to the slicer.
And create a measure in employee table:
Display = IF(ISFILTERED(Manager[EmployeeName]),CALCULATE(SUM('employee'[FTE]),FILTER('employee',CONTAINSSTRING([ManagerHierarchy],MAX(Manager[EmployeeID])))),SUM(employee[FTE]))Output:
Best Regards!
Yolo Zhu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
