i am having trouble creating a pivot table from the consolidation of two data tables. i am using only 1 field from each data table in the pivot table. This field is common to both tables. its called Status.
so, its the row name and then i am trying to do a count of the same field. the end result should be a count of records from both data tables by their status.
I have tried two methods: the first is to create the pivot table off the first data table, add it to the data model by selecting the box at the bottom of the piv table wizard, then selecting All from the Pivot Table fields menu. I then select the Status field as Row and Status field again to count. This gives me a pivot table which correctly counts the records by status counts from the first data table. Then i select the Status field from the second data table in the Pivot Fields menu, adding it to rows and to data. when i add it to the Rows area, i get this below where it appears to include all statuses (from the data tables under each specific status (sort of like a sub status) from the first data table and gives the count of the records from the first data table status against each of the sub statuses. the 1564 is actually the number of approved records in the first data table.
what i want to see is a pivot table combining the counts of records for each status from each data table.
so, its the row name and then i am trying to do a count of the same field. the end result should be a count of records from both data tables by their status.
I have tried two methods: the first is to create the pivot table off the first data table, add it to the data model by selecting the box at the bottom of the piv table wizard, then selecting All from the Pivot Table fields menu. I then select the Status field as Row and Status field again to count. This gives me a pivot table which correctly counts the records by status counts from the first data table. Then i select the Status field from the second data table in the Pivot Fields menu, adding it to rows and to data. when i add it to the Rows area, i get this below where it appears to include all statuses (from the data tables under each specific status (sort of like a sub status) from the first data table and gives the count of the records from the first data table status against each of the sub statuses. the 1564 is actually the number of approved records in the first data table.
what i want to see is a pivot table combining the counts of records for each status from each data table.