SmeeK

2019-07-18, 02:02 PM

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!!!