Measure to combine two fact sheets

SmeeK

New member
Joined
Jul 18, 2019
Messages
3
Reaction score
0
Points
0
Excel Version(s)
2016
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!!!
 

Attachments

  • Power query logic.xlsx
    317.7 KB · Views: 8
Hi ,


Actualy you didn't work that bad...

Just some remarks:

Normaly you have the qty produced per Work center
You should create measure & use them in the pivots

ItemD108 : you made an error un the quantity.
 

Attachments

  • Power query logic.xlsx
    328.8 KB · Views: 4
You should create measure & use them in the pivots -> got it, will do, thanks :)

Normally you have the qty produced per Work Center -> that is the problem. I tried calculate with various filters, but I couldn't get the pivot to return the correct amounts

Hi BernardBrussels,
thanks a lot for your answer and the file. Unfortunately the results are not what I wanted.
The quantity shows the total quantity, regardless if the work Center was involved in the production order or not.
I agree, I need a measure to show the quantity on work Center level. I tried various variations of calculate with filters, but didn't get the required result.
I could always go the long way and pull the quantity column via RELATE into the minutes table (after first grouping the minutes table), but I am reluctant to do this, if a measure can solve the problem.

Thanks again for your help and answer.
 

Attachments

  • Power query logic_answer.xlsx
    331.3 KB · Views: 0
Measure missing

Hello BernardBrussels,

thank you very much for your answer.
I actually replied last week, but most have pressed the wrong button. Somehow my reply has not appeared here.
Unfortunately my problem has not been solved.

You should create measure & use them in the pivots -> got it, will do :)
Normally you have the qty produced per Work Center -> that is the problem, I agree that this is necessary, but I haven't found out how. Most likely a calculate formula with a filter.
At the moment the pivot is not correct. It shows the quantity for all the items, no matter if the work Center has worked on them or not.
I have marked the incorrect numbers in the pivot and tried a calculated measure. But I doesn't filter on the work Center.
I could of course pull the quantity per RELATE into the minutes table (after grouping the table per production order and work Center). But I do think that this is exactly what measures are for.


Thanks again for your help and I hope someone can help me with a working measure.
 

Attachments

  • Power query logic_answer.xlsx
    342.7 KB · Views: 2
Back
Top