Pivot with Data Model over 3 simple tables is not working correctly
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.
Basically:
$$ 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
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.
Basically:
$$ 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
ASKER
Nothing should show - they would not show up in the list (pivots) - basically the focus of the pivots is to analyze "issue" information - i.e determine value per issue, show which customers have the issues and the value, show issue value per region/market, issue value per industry - these will be split onto separate worksheets and then will have slicers of the other "Attributes". For example, if I have a pivot that shows value per Issue Category - then I would have slicers on that pivot/worksheet for Industry, Region, Market etc
Thank you for noticing that - they are just customers - for which we have not yet encountered any issues. So they would be ignored in the pivots.
Thank you for noticing that - they are just customers - for which we have not yet encountered any issues. So they would be ignored in the pivots.
ASKER
I have amended the file somewhat - it is feasible for us to remove customers without issues off the list (so I have removed them from this new file).
I have also created the analysis closer to how we need it - basically the various analyses are spread across worksheets - with appropriate slicers to "slice and dice" the information based upon the various attributes.
So if there is anyone that can provide guidance and insight on how to ensure the math is "correct based upon what is shown and filtered - then I am all ears.
Thanks!
Issue-Tracking-Model-2.xlsx
I have also created the analysis closer to how we need it - basically the various analyses are spread across worksheets - with appropriate slicers to "slice and dice" the information based upon the various attributes.
So if there is anyone that can provide guidance and insight on how to ensure the math is "correct based upon what is shown and filtered - then I am all ears.
Thanks!
Issue-Tracking-Model-2.xlsx
I think what you are trying to achieve is possible using Power Query merging of tables and the only issue I see is the column of value. because when we merge the tables, we do not know how the values of customer can be split into the issues and categories and sub categories, because if the value is left as it is then it may count more than one value for customers.
I have recorded a 2 minutes video for you that demonstrates how you can merge your tables into one and then use the pivot.
hope it helps.
Issue-Tracking-Model-2--1-.xlsx
For-Steve.mp4
I have recorded a 2 minutes video for you that demonstrates how you can merge your tables into one and then use the pivot.
hope it helps.
Issue-Tracking-Model-2--1-.xlsx
For-Steve.mp4
ASKER
Thanks ProfessorJimJam!! That is a great question - and exactly the issue.
I will review the videos.
The way we wish to see the value (and that is the tricky part :-) ) - is that we only want to count the value of each customer "once" irrespective of the analysis we view.
In other words:
If I look at Value by Issue - then I need to sum the value of each customer for which that Issue occurred to give me a total value affected by this Issue (with ability to expand the "Issue" to show the specific customer(s) affected and their "Value" (Analysis By Issue worksheet)
If I look at Analysis by Customer - then I wish to see the Total Value of that Customer and able to expand to show the Issues affecting that Customer
If I look at Analysis By Category - then I wish to see the total Value of the Customers affected by Issues within that Category (it is likely that a Customer may have been affected by more than one Issue within a single Category - but I only wish to count the Customer value "once")
If I look at Analysis by Industry - then I wish to see the total Value of the Customers within an Industry (this one works as the data for Industry is on the same Table as the Customer) - however it gets a little tricky if you then start using the slicers to only show Value by Industry when I select a particular Issue Category for example (i.e. that slicer has zero effect)
I hope that all makes sense - I will review the videos.
Thanks!!
I will review the videos.
The way we wish to see the value (and that is the tricky part :-) ) - is that we only want to count the value of each customer "once" irrespective of the analysis we view.
In other words:
If I look at Value by Issue - then I need to sum the value of each customer for which that Issue occurred to give me a total value affected by this Issue (with ability to expand the "Issue" to show the specific customer(s) affected and their "Value" (Analysis By Issue worksheet)
If I look at Analysis by Customer - then I wish to see the Total Value of that Customer and able to expand to show the Issues affecting that Customer
If I look at Analysis By Category - then I wish to see the total Value of the Customers affected by Issues within that Category (it is likely that a Customer may have been affected by more than one Issue within a single Category - but I only wish to count the Customer value "once")
If I look at Analysis by Industry - then I wish to see the total Value of the Customers within an Industry (this one works as the data for Industry is on the same Table as the Customer) - however it gets a little tricky if you then start using the slicers to only show Value by Industry when I select a particular Issue Category for example (i.e. that slicer has zero effect)
I hope that all makes sense - I will review the videos.
Thanks!!
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks ProfessorJimJam - while this did not resolve all of the issues - it has set me on a path that significantly helps. It provided a baseline - as it happens in order to make some of the values work correctly I needed to create some calculated measures (taking the value and dividing by the number of Issues or Customers - depending upon the analysis).
As it happens this did not fully resolve the issue either - the only way I found to do it in some cases when attempting to craft analyses by say Category or Industry was to copy data from source tables into the X Ref table via formulas (the categories and value) - then for some of the combinations create unique "keys" - and then determine a count of the unique keys and divide the value by the "key count". Then when I bring this into the Analyses it works (at least it is effective for what I need and "accurate" at the summation level).
Thanks once again!
As it happens this did not fully resolve the issue either - the only way I found to do it in some cases when attempting to craft analyses by say Category or Industry was to copy data from source tables into the X Ref table via formulas (the categories and value) - then for some of the combinations create unique "keys" - and then determine a count of the unique keys and divide the value by the "key count". Then when I bring this into the Analyses it works (at least it is effective for what I need and "accurate" at the summation level).
Thanks once again!
apparently, the following customers do not have any issues associated with them based on the common table of XRef
what should show for these customers when there is no issue for them?
Customer 35
Customer 36
Customer 37
Customer 41
Customer 43
Customer 51
Customer 61