Forum Discussion

Mark_Everett's avatar
Mark_Everett
Copper Contributor
Oct 07, 2020

Incomplete/incorrect relations between two tables in Excel Power Pivot Data Model

Greetings Excel Community - this is my first posting here..  Seeking expertise in building a Power Pivot Table leveraging two different tables of data loaded into the data model using the latest subscription instance of Microsoft Excel 365.  I'm struggling to create properly working relationships between the tables within the datamodel.  Each table represents labor data for a series of independent work tasks tracked within tickets called Repair Orders (or 'ROs').  It's an automotive shop environment with multiple 'Techs' (the hourly laborer) and their tasks/jobs.  The Repair Order numbers are unique, and may or may not exist in both tables.  Associated with each RO are two different types of job time data:  1) actual 'logged' time data (in fractions of an hour), and 2) amount of labor time billed out to the customer, often a 'flat-rate', or fixed charge for a task.  In theory, these two labor values could be the same, but there is often variation due to efficiencies, unplanned issues, etc.    

 

The actual labor data is (usually) logged into the ticket across multiple dates as discrete entries.  So each row of data has:  'RO number', 'date', 'labor-time'.

 

The billed labor data is comprised of multiple tasks, each with an amount of labor time, so this data looks like:

Row1: 'RO number', 'labor-time 1'

Row2: 'RO number', 'labor-time 2'

 

RO #                       LaborHoursPerTask

2028252
2028252.5
2028252
2028252.5
2028251.5
2028258
2028250.5
2028250.5
2028252
2028257
2028254.5
20283142
2028330.3
2028330
2028330
2028542.5
2028542
20285413.2

</snip>

 

Snippets of data from actual labor hours logged per RO, per date:

RO#             InvoiceDate      LaborHours

2028257/29/20202
2028257/29/20202.5
2028257/29/20202
2028257/29/20202.5
2028257/29/20201.5
2028257/29/20208
2028257/29/20200.5
2028257/29/20200.5
2028257/29/20202
2028257/29/20207
2028257/29/20204.5
2028318/2/202042
2028338/2/20200.3
2028338/2/20200
2028338/2/20200
2028548/6/20202.5
2028548/6/20202
2028548/6/202013.2

 

I want to create a table which displays the total amount of both types of data - 'actual' vs 'billed' - for each RO ticket.  Here's the result I'm currently getting, and you can see the logged hours are improperly all rolled up into a total sum value, instead of the discrete amount per RO.

 

RO                   SumofBilledHours  SumofLoggedHours

2028253312704.83
2028314212704.83
2028330.312704.83
20285428.812704.83
20287037.712704.83
20287133.712704.83

4 Replies

  • SergeiBaklan's avatar
    SergeiBaklan
    Diamond Contributor

    Mark_Everett 

    With data model only you may add both tables to data model and create two measures:

    Logged Hours:=SUM(Logged[Hrs])
    
    and
    
    Billed Hours:=CALCULATE (
       SUM ( Billed[Hrs] ),
       TREATAS (
           VALUES ( Logged[RO #] ),
           Billed[RO#]
       )
    )

    after that create PivotTable from data model as

    • cuong's avatar
      cuong
      Icon for Microsoft rankMicrosoft

      Mark_Everett Did this help?  Please Mark as Best Response to help others with similar questions.  Thanks!

       

      • SergeiBaklan's avatar
        SergeiBaklan
        Diamond Contributor

        cuong , both are workable, it depends on context. If Mark is more comfortable with Power Query then definitely Riny_van_Eekelen  solution is the "best". If Mark prefers DAX manipulations then the second one.

         

        In Excel practically everything could be done by several ways. Which one to prefer depends on concrete person preferences, expected efforts in maintenance, integration with other functionality which is not visible behind concrete question and just on emotions. 

         

        At the same time, from supporting of this resource point of view (recognition, visibility, etc SEO stuff) it's better to have any "best" than no "best" at all. However, for visitors I'd recommend to check entire thread (in general, not this concrete thread) , not only the question and best answers but all discussion. That could help in concrete situation which could be slightly differ from mentioned in "best" answer.

         

        Thus Mark_Everett , if you like MTC and willing to support it, please make your choice. That's business, not private.

  • Riny_van_Eekelen's avatar
    Riny_van_Eekelen
    Platinum Contributor

    Mark_Everett You can't have "many-to-many" relationships in the DM. In the attached file I grouped the Billed and Logged tables by RO# before merging (and load back to Excel) or loading them Into the DM (to create a pivot table). See if this resolves your problem.

Resources