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

%3CLINGO-SUB%20id%3D%22lingo-sub-1157351%22%20slang%3D%22en-US%22%3EData%20Model%20%26amp%3B%20Pivot%20Table%20Sums%20are%20Aggregate%20not%20based%20on%20model%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1157351%22%20slang%3D%22en-US%22%3E%3CP%3EHello%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20built%20a%20data%20model%20and%20am%20trying%20to%20summarize%20my%20data.%26nbsp%3B%20Not%20sure%20why%20but%20each%20row%20of%20my%20pivot%20table%20gives%20an%20aggregate%20sum%20of%20all%20the%20data%20rather%20than%20the%20row's%20information.%26nbsp%3B%20The%20Key%20item%20is%20%22Employee%20Name%22%20which%20relates%20to%20the%20employee%20name%20in%20two%20lists%20of%20salary%20and%20other%20data%20per%20employee%20for%20two%20different%20years.%26nbsp%3B%20A%20select%20few%20employees%20are%20on%20one%20of%20two%20projects.%3C%2FP%3E%3CDIV%20class%3D%22mceNonEditable%20lia-copypaste-placeholder%22%3E%26nbsp%3B%3C%2FDIV%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22data%20model.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F169561i10D0AA09D4798858%2Fimage-size%2Fmedium%3Fv%3D1.0%26amp%3Bpx%3D400%22%20title%3D%22data%20model.png%22%20alt%3D%22Data%20Model%22%20%2F%3E%3CSPAN%20class%3D%22lia-inline-image-caption%22%20onclick%3D%22event.preventDefault()%3B%22%3EData%20Model%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Relationships.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F169562iF14802A6724E0F9A%2Fimage-size%2Fmedium%3Fv%3D1.0%26amp%3Bpx%3D400%22%20title%3D%22Relationships.png%22%20alt%3D%22Relationships.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3EThe%20resulting%20pivot%20table%20is%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Pivot%20table.png%22%20style%3D%22width%3A%20781px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F169560iB5F35A9571FC72BB%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20title%3D%22Pivot%20table.png%22%20alt%3D%22Pivot%20Table%22%20%2F%3E%3CSPAN%20class%3D%22lia-inline-image-caption%22%20onclick%3D%22event.preventDefault()%3B%22%3EPivot%20Table%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3EIn%20my%20pivot%20table%2C%20the%20sums%20are%20not%20giving%20a%20subtotal%20by%20employee.%26nbsp%3B%20Instead%2C%20it%20is%20adding%20all%20the%20salaries%20for%20all%20employees.%26nbsp%3B%20When%20I%20double-click%20any%20summed%20amount%2C%20it%20brings%20up%20the%20entire%20list%20of%20data%2C%20not%20just%20items%20for%20that%20employee.%26nbsp%3B%20What%20am%20I%20doing%20wrong%3F%3C%2FP%3E%3CP%3E%3CBR%20%2F%3EFurther%2C%20if%20I%20go%20to%20value%20field%20settings%20for%20my%20calculated%20fields%2C%20which%20are%20set%20to%20sum%20currently%2C%20I%20get%20options%20to%20%22summarize%20value%20field%20by%22%20rather%20than%20the%20regular%20subtotal%20tab.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1157351%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1161115%22%20slang%3D%22en-US%22%3ERe%3A%20Data%20Model%20%26amp%3B%20Pivot%20Table%20Sums%20are%20Aggregate%20not%20based%20on%20model%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1161115%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F548130%22%20target%3D%22_blank%22%3E%40TREXcel%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIs%20there%20a%20reason%20why%20you%20need%20the%20Project%20Employees%20and%20ProjectAllSalaries%20tables%20in%20your%20model%3F%3C%2FP%3E%3CP%3EDoes%20the%20model%20work%20without%20these%20tables%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIf%20you're%20looking%20to%20aggregate%20by%20project%2C%20what%20relationship%20do%20the%20projects%20have%20to%20the%20IDMCT%20tables%3F%20Would%20there%20be%20any%20way%20to%20include%20the%20project%20in%20these%20tables%20and%20then%20bypass%20the%20Employee%20List%20table%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
Occasional Visitor

Hello,

 

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

Relationships.png

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.

 

1 Reply

@TREXcel 

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?