Data Model & Pivot Table Sums are Aggregate not based on model

Occasional Visitor



I have built a data model and am trying to summarize my data.  Not sure why but each row of my pivot table gives an aggregate sum of all the data rather than the row's information.  The Key item is "Employee Name" which relates to the employee name in two lists of salary and other data per employee for two different years.  A select few employees are on one of two projects.


Data ModelData Model


The resulting pivot table is


Pivot TablePivot Table

In my pivot table, the sums are not giving a subtotal by employee.  Instead, it is adding all the salaries for all employees.  When I double-click any summed amount, it brings up the entire list of data, not just items for that employee.  What am I doing wrong?

Further, if I go to value field settings for my calculated fields, which are set to sum currently, I get options to "summarize value field by" rather than the regular subtotal tab.


2 Replies


Is there a reason why you need the Project Employees and ProjectAllSalaries tables in your model?

Does the model work without these tables?


If you're looking to aggregate by project, what relationship do the projects have to the IDMCT tables? Would there be any way to include the project in these tables and then bypass the Employee List table?


@TREXcel I see very similar behavior when trying to aggregate customer spend from several different sources. I get the 'right' amount for the table the Pivot was originally created from, but the sum of the column for any related tables. Did you ever figure this out?