We help IT Professionals succeed at work.
Get Started

Pivot with Data Model over 3 simple tables is not working correctly

Last Modified: 2018-11-12
Pivot - even with Data Model is not working correctly.
I have created a workbook that contains three tabs - each tab contains a table.
This file will be loaded into Sharepoint so users in the target group can add data online - so no VBA is allowed.

The three tables are as follows:
1. Customers - simple list of customers with a couple of values that categorize the customer (Region, Market, Industry for example) - and also a $$ value of that customer. This is added to on a regular basis as we work with new customers .
2. Issues - a simple list of typical issues that are encountered by customers. This list rarely changes - but it can be added to as new issues are identified. This list also contains categorizations of the issues (Category and Sub Category)
3. Customer/Issue Cross reference - this table is a simple cross reference. Customer to Issues encountered.

When we have a new customer with issues - we add the customer to the customer table (so we only define the customer and their attributes once), and then we access the Cross Reference table - and then define potentially multiple rows - the customer will be repeated for each issue that we wish to list. So if a customer encountered 3 issues there would be three rows on this table - the customer would be listed three times (in the first column) and then each issues would be listed against the customer in a separate row.
The data is very simplistic.
However - here is the issue I am having. I have created a Data Model using the three tables - I link the X Ref to the Customer Table (one to many relationship), and I link the Issues to the XRef table (again a one to many relationship).

Nothing I do seems to allow me to get pivot tables over the data the way I want to see it.
$$ Impacted by Issue
$$ Impacted by Issue Category
$$ Impacted by Sub Category
etc etc (there are several I need to create - and I want to also add slicers to the Pivots to allow users to do some slicing and dicing).

The pivot always seems to summarize the $$value incorrectly.

I have attached a file as an example that shows exactly what I am encountering. This include the three tables, along with the Data Model with relationships defined, and a few example Pivots that show the problem (note this is not how I will layout the final workbook pivots if I ever can get these values to show correctly - the ones in here are just to show the issue).

If anyone has thoughts on how I can get this set up appropriately to make it work correctly then all suggestions are welcome.
Watch Question
Spreadsheets Expert
Top Expert 2014
This problem has been solved!
Unlock 1 Answer and 7 Comments.
See Answer
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE