Results 1 to 4 of 4

Thread: Measure to combine two fact sheets

Hybrid View

Previous Post Previous Post   Next Post Next Post
  1. #1
    Neophyte SmeeK's Avatar
    Join Date
    Jul 2019
    Posts
    3
    Articles
    0
    Excel Version
    2016

    Measure to combine two fact sheets

    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

    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…

    I have tried to add a small example of excel file that should explain the problem better.

    Thank you!!!
    Attached Files Attached Files

  2. #2
    Seeker BernardBrussels's Avatar
    Join Date
    Mar 2019
    Posts
    9
    Articles
    0
    Excel Version
    excel 365
    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.
    Attached Files Attached Files

  3. #3
    Neophyte SmeeK's Avatar
    Join Date
    Jul 2019
    Posts
    3
    Articles
    0
    Excel Version
    2016
    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.
    Attached Files Attached Files

  4. #4
    Neophyte SmeeK's Avatar
    Join Date
    Jul 2019
    Posts
    3
    Articles
    0
    Excel Version
    2016

    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.
    Attached Files Attached Files

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •