Forum Discussion
Linking multiple tables for one visual
Step 0: I use your data below. (Date: yyyy/mm/dd)
Step 1: I add a 'WBS Element' column to the 'Remedy customer list' table.
WBS Element = [Cat match]&" - "&[Order type]
Step 2: I make a 'Calendar' table.
Step 3: I make a 'WBS-List' table.
WBS-List = SUMMARIZE('Timesheet data','Timesheet data'[WBS Element])
Step 4: I add some relationships below.
Step 5: I make some tables and some matrixs.
Hi Mickey,
Thank you very much for taking the time to look into this for me. Here's some dummy data:
| Table 1: Timesheet data | |||
| Employee | Date | Qty of hours | WBS Element |
| Joe Blogs | 01/04/2024 | 4.5 | Internal Meetings |
| Joe Blogs | 01/04/2023 | 2 | External customer 1 - Provision |
| Joe Blogs | 01/04/2023 | 1 | External customer 1 - Cease |
| Cat Smith | 01/04/2024 | 7.5 | Internal Meetings |
| Roger Ralph | 01/04/2024 | 7.5 | Admin |
| Joe Blogs | 02/04/2024 | 7.5 | External Meetings |
| Cat Smith | 02/04/2024 | 4 | Admin |
| Cat Smith | 02/04/2024 | 3.5 | External customer 2 - Cease |
| Roger Ralph | 02/04/2024 | 7.5 | Admin |
| Table 3: Remedy customer list | ||
| Cat match | Order type | Remedy customer |
| Interal customer 1 | Provision | Scotland |
| Interal customer 1 | Cease | Scotland |
| Internal customer 2 | Provision | London |
| Internal customer 2 | Cease | London |
| Table 2: Remedy data | |||
| WO ID | Order type | Remedy customer | Submit date |
| WO001 | Provision | Scotland | 01-Apr |
| WO002 | Cease | Scotland | 01-Apr |
| WO003 | Provision | Scotland | 01-Apr |
| WO004 | Cease | London | 02-Apr |
Step 0: I use your data below. (Date: yyyy/mm/dd)
Step 1: I add a 'WBS Element' column to the 'Remedy customer list' table.
WBS Element = [Cat match]&" - "&[Order type]
Step 2: I make a 'Calendar' table.
Step 3: I make a 'WBS-List' table.
WBS-List = SUMMARIZE('Timesheet data','Timesheet data'[WBS Element])
Step 4: I add some relationships below.
Step 5: I make some tables and some matrixs.
Hi Mickey, thank you for doing that. I've managed to replicate what you've got on my table. The only difference is, the one-to-one relationship is a one-to-many WBS-list (one) to Remedy customer list (many).
Now how can I take this and make a visualisation bar chart with the WBS Element in the X Axis and then the Sum of quantity (hours from timesheet data) and the count of WO as another bar, both in the Y Axis? The Sum of quantity is showing correctly in the Y axis but the count of WO is just counting all WOs in every X axis category.
How about this graph?
This problem of 'the count of WO' could be solved if there was a key column in 'Remedy data' table to set a relationship with 'WBS-List' table, but I am unable to create a key column because I do not understand the meaning of each piece of data.
Thank you so much for sticking with me here. So this is what I'm trying to achieve but when adding the Count of WO ID, it is giving the same number for each WBS element category, but it should be showing:
External Customer 1 - provision: 2External Customer 1 - Cease: 1
External Customer 2 - Cease: 1