Forum Discussion
Pivot Table - 2 criteria but show only one
Hi 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).
Hi 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).
- Thanks, Martin -
This was pretty much the answer I was expecting. Even hiding the ID column, if I sort by Name first, it consolidates the duplicate Names. So, I'm back to where I started. For now, I'm waiting to hear from HR and those to whom I send the report if sending only employee ID's or both ID and Name (sorted by EID first) is a viable solution.
It sounds like we may need to update the employee records to avoid duplicates in the first place, but I don't know how that will work (I don't maintain that system). Plus, we have over 50k employees company-wide and a lot of possible duplicates. So, that would be a lot of work...again, for someone else.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:
