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.