Forum Discussion
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
| 202825 | 2 |
| 202825 | 2.5 |
| 202825 | 2 |
| 202825 | 2.5 |
| 202825 | 1.5 |
| 202825 | 8 |
| 202825 | 0.5 |
| 202825 | 0.5 |
| 202825 | 2 |
| 202825 | 7 |
| 202825 | 4.5 |
| 202831 | 42 |
| 202833 | 0.3 |
| 202833 | 0 |
| 202833 | 0 |
| 202854 | 2.5 |
| 202854 | 2 |
| 202854 | 13.2 |
</snip>
Snippets of data from actual labor hours logged per RO, per date:
RO# InvoiceDate LaborHours
| 202825 | 7/29/2020 | 2 |
| 202825 | 7/29/2020 | 2.5 |
| 202825 | 7/29/2020 | 2 |
| 202825 | 7/29/2020 | 2.5 |
| 202825 | 7/29/2020 | 1.5 |
| 202825 | 7/29/2020 | 8 |
| 202825 | 7/29/2020 | 0.5 |
| 202825 | 7/29/2020 | 0.5 |
| 202825 | 7/29/2020 | 2 |
| 202825 | 7/29/2020 | 7 |
| 202825 | 7/29/2020 | 4.5 |
| 202831 | 8/2/2020 | 42 |
| 202833 | 8/2/2020 | 0.3 |
| 202833 | 8/2/2020 | 0 |
| 202833 | 8/2/2020 | 0 |
| 202854 | 8/6/2020 | 2.5 |
| 202854 | 8/6/2020 | 2 |
| 202854 | 8/6/2020 | 13.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
| 202825 | 33 | 12704.83 |
| 202831 | 42 | 12704.83 |
| 202833 | 0.3 | 12704.83 |
| 202854 | 28.8 | 12704.83 |
| 202870 | 37.7 | 12704.83 |
| 202871 | 33.7 | 12704.83 |
4 Replies
- SergeiBaklanDiamond Contributor
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
Microsoft
Mark_Everett Did this help? Please Mark as Best Response to help others with similar questions. Thanks!
- SergeiBaklanDiamond 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_EekelenPlatinum 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.