Sep 07 2022 02:21 PM
I have a labor file with data source sorted by several criteria including Employee ID and then by Pay Code (Regular Hours, OT hours, PTO, etc.). I have a Pivot Table that pulls from that based on Labor Assignment first - A Shift, B Shift, A Shift 2nd Location, B Shift 2nd Location, ... And then pulls the Employee Name and total regular hours (Pivot Table has a manual filter for that Pay Code).
My problem is I have two employees with the same name, but different Employee ID's (actually have a few of these "duplicates") who work under the same Labor Assignment. I want the table to sort them out by Employee ID, but only show the name in the results.
for example:
A Shift - Employee ID 1234 - John Smith 8 hours
A Shift - Employee ID 2345 - John Smith 8.5 hours
I want the table to output:
A Shift
John Smith 8 hours
John Smith 8.5 hours
Right now it is showing
John Smith 16.5 hours
or
John Smith 1234 8 hours
John Smith 2345 8.5 hours
Is it possible to sort on a field but not show it in the table? Perhaps, do I need to do something with the source data as far as sorting or adding a column?
Thanks in advance.
p.s. - don't want / can't use Power Query due to constraints at work.
Sep 08 2022 12:33 AM
SolutionHi @RandomPanda
in a regular pivot table you can only sort by fields which are visible in the pivot table. In Power Pivot / datamodel there is an option to sort a column by another column.
But even then this would not help in your case, because since the name of the employee is the same for all ID's, it will group the result by the name. So the output in the pivot table will be just one line with the name and the sum of all hours, as you experienced already.
The only solution I can think of is keeping the ID in the pivot table and just hide the ID column (I know this is not an ideal solution).
Sep 08 2022 06:40 AM
Sep 08 2022 06:50 AM
Hi @RandomPanda
one additional hint concerning the hidden column and the consolidation of the names:
You can adjust the field settings for the name field, so every name gets repeated (if this is what you really need).
Here is an example:
Sep 08 2022 12:33 AM
SolutionHi @RandomPanda
in a regular pivot table you can only sort by fields which are visible in the pivot table. In Power Pivot / datamodel there is an option to sort a column by another column.
But even then this would not help in your case, because since the name of the employee is the same for all ID's, it will group the result by the name. So the output in the pivot table will be just one line with the name and the sum of all hours, as you experienced already.
The only solution I can think of is keeping the ID in the pivot table and just hide the ID column (I know this is not an ideal solution).