Forum Discussion
Solved
Pivot Table from Related Tables
Hi all, I'm trying to display a bunch of data in a pivot table, but I'm having trouble getting it to show up like I want it to. My data is divided into multiple tables, but I believe setting up r...
dhofland When creating a pivot table with fields from related tables, you need to have at least one field in the Values area. Drag the unused "Name" field from the "Repeated" table to the Values area to return only the related "Jobs" for each person...
Pivot Table with Related Records
Also, to answer your second question, if you're referring to the option to "Sort in data source order", this applies to the "Field List" shown in the PivotTable Fields Pane only...
Field List Options
dhofland When creating a pivot table with fields from related tables, you need to have at least one field in the Values area. Drag the unused "Name" field from the "Repeated" table to the Values area to return only the related "Jobs" for each person...
Pivot Table with Related Records
Also, to answer your second question, if you're referring to the option to "Sort in data source order", this applies to the "Field List" shown in the PivotTable Fields Pane only...
Field List Options
djclements Thanks for that answer - that's what I was missing! I do have a couple of follow-up questions:
1. Is there a way to hide the "Count of Name" column from the Pivot Table, or would I have to hide all of column "N" to do that?
2. How could I add another table of repeated values to the Pivot Table and do the same thing? For example, could I create a table of character traits (charismatic, introvert, personable, etc...) with multiple traits per person (and create a relationship between the "Name" column of both tables) and add that to the pivot table the same way?
3. As for the data source order, I was referring to the filter dropdown from the "Name" column of the pivot table. I want the names to remain in the order listed in the "General Info" table.
dhofland Hiding column N would be easiest, but if you don't want to do that, you can also apply some custom formatting to the "Count of Name" column. Right-click on any value in the column and select Value Field Settings... Overwrite the "Custom Name" with a single space, then go to Number Format > Custom, enter three semi-colons (;;;) for the "Type" and click "OK" twice.
Value Field Settings > Number Format
This will essentially hide the values in column N of the pivot table. You can also hide the totals row, if desired. On the ribbon, go to PivotTable Design > Grand Totals > Off for Rows and Columns. The final result would look something like this:
Final Layout
Notice how the names have been rearranged in the desired order. This can be done manually by selecting a name, then moving your cursor to the bottom border of the selected cell until the mouse pointer changes to the "move" crosshairs, at which point you can just click and drag the name up or down to rearrange the order. I don't believe there is a way to do this automatically, other than by creating custom lists.
As to your inquiry about adding another one-to-many table relationship, it would be simpler to use Power Query to create the appropriate table joins and load the resulting query to a Pivot Table Report. Although, if your intention is to show all fields for all records together in one report, at least one of the fields will return duplicate values (due to the many-to-many relationship between the two tables with "many" records).
Pivot Table from Power Query
- djclements Thanks for all your help on this. Looks like I have some decisions to make on whether I want to delve into Power Query.