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.
I think the cause are some "many-to-many" relationships.
The amount of data doesn't need to be large, so if you can provide me with some concrete 'demo' data I can suggest solutions to improve the situation.
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.