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. Issue-Tracking-Model.xlsx
”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
With your subscription - you'll gain access to our exclusive IT community of thousands of IT pros. You'll also be able to connect with highly specified Experts to get personalized solutions to your troubleshooting & research questions. It’s like crowd-sourced consulting.
We can't always guarantee that the perfect solution to your specific problem will be waiting for you. If you ask your own question - our Certified Experts will team up with you to help you get the answers you need.
Our certified Experts are CTOs, CISOs, and Technical Architects who answer questions, write articles, and produce videos on Experts Exchange. 99% of them have full time tech jobs - they volunteer their time to help other people in the technology industry learn and succeed.
We can't guarantee quick solutions - Experts Exchange isn't a help desk. We're a community of IT professionals committed to sharing knowledge. Our experts volunteer their time to help other people in the technology industry learn and succeed.