Hello out there,
I am trying to get my head around a probably easy problem. I just seem to be missing some basic datamodelling knowledge or measure knowledge.
I have ben trying to get this solved for a week now, and I am a bit frustrated on my inability to solve this. I am sure it is not difficult.
So after a week, I decided to register in this excellent forum in the hope of finding my personal hero :hail:
I have two tables of information, both containing facts, and I need to somehow combine them into one pivot without getting the error from the pivot, that I am missing a connection.
Here is the data I have:
Table 1: minutes booked by different work centres onto different production orders
Table 2: List of all production orders including the date finished, the item produced and the quantity produced
As a third and fourth table I have added a list of items and a list of work centers as dimension tables.
The pivot table in the end should show me for a given time period and a given work center the list of items it produced with the quantity and time.
As a calculation I will then add the time it took to produce one item.
The problem is, that although I can filter in the pivot on the list of items per work center and add the time, as soon as I add the quantity, the list of production orders shows all production orders. Power Pivot at this stage cannot know what the quantity is per work center.
I am not sure if the solution is to add some help table or if it can be solved via a measure.
I hope someone can help me…:ballchain:
I have tried to add a small example of excel file that should explain the problem better.
Thank you!!!
I am trying to get my head around a probably easy problem. I just seem to be missing some basic datamodelling knowledge or measure knowledge.
I have ben trying to get this solved for a week now, and I am a bit frustrated on my inability to solve this. I am sure it is not difficult.
So after a week, I decided to register in this excellent forum in the hope of finding my personal hero :hail:
I have two tables of information, both containing facts, and I need to somehow combine them into one pivot without getting the error from the pivot, that I am missing a connection.
Here is the data I have:
Table 1: minutes booked by different work centres onto different production orders
Table 2: List of all production orders including the date finished, the item produced and the quantity produced
As a third and fourth table I have added a list of items and a list of work centers as dimension tables.
The pivot table in the end should show me for a given time period and a given work center the list of items it produced with the quantity and time.
As a calculation I will then add the time it took to produce one item.
The problem is, that although I can filter in the pivot on the list of items per work center and add the time, as soon as I add the quantity, the list of production orders shows all production orders. Power Pivot at this stage cannot know what the quantity is per work center.
I am not sure if the solution is to add some help table or if it can be solved via a measure.
I hope someone can help me…:ballchain:
I have tried to add a small example of excel file that should explain the problem better.
Thank you!!!