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.