troubleshooting Question

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

Avatar of Steve Monk
Steve MonkFlag for United States of America asked on
Microsoft SharePoint* Pivot TablesMicrosoft ExcelMicrosoft Office
7 Comments1 Solution107 ViewsLast Modified:
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.
Professor JSpreadsheets Expert

Our community of experts have been thoroughly vetted for their expertise and industry experience.

Top Expert 2014

The Distinguished Expert awards are presented to the top veteran and rookie experts to earn the most points in the top 50 topics.

Join our community to see this answer!
Unlock 1 Answer and 7 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 7 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros