I want to be able to run a calculation between two tables where one of the columns in the tables are in a relationship. for some reason whatever I have tried isn't working.
here is a sample of what i've built so far:
|Main Data Table|
|Customer||Utilisaton||Emp Name 1||Emp Name 2|
|Cust 1||10%||Joe Bloggs|
|Cust 2||15%||Jane Bigs||Joe Bloggs|
|Cust 3||10%||Joe Bloggs|
|Cust 4||20%||Jane Bigs||Joe Bloggs|
|Cust 5||10%||Jane Bigs|
|Cust 6||15%||Jane Bigs|
Employee table 1 which has a relationship to Data Table Emp Name 1, this runs a calculation that looks for the Emp Name in the data table Emp Name 1 column and then sum the utilization associated. I've also used it to bring through the results from a second table and create a total (this is the part I want to change)
|Emp List table 1|
|Emp Name1||Utilization||Emp 2 Utilization||Total Utilization|
Employee Table 2, has a relationship with Data Table Emp Name 2, does the same as the first table simply calculates the utilzation based on finding the name in Emp Name 2.
|Emp Table 2|
Ideally I want to have one Employee table that does the calculation for all the utilization, but I can't work out how to have that work when I need to look in two different columns. (Emp Name 1 and Emp Name 2). and its not possible to combine the two as the names are the same and the reason for them being there is different.
I doubt this is the quickest way to do it, but you could use "group by"
1) Create duplicate of Main Data query in query editor (So you have Query 1 and Query 2 - Or whatever it might be named)
2) In Query 1, Group by "Emp Name 1" (New Column Name: "Utilisation", Operation: "Sum", Column: "Utilisation")
3) In Query 2, Group by "Emp Name 2" (New Column Name: "Emp 2 Utilisation ", Operation: "Sum", Column: "Utilisation")
4) In Query 2 change column name from "Emp Name 2" to "Emp Name 1"
5) Remove empty values from "Emp Name 1" column in both queries (will only affect one query visibly)
6) Append Queries as new (Choose Query 1 and Query 2)
7) In new appended query (Append 1) Remove all columns other than "Emp Name 1" (Use Remove all other columns while it's selected)
8) In appended query remove duplicate rows
9) Merge queries (Select Append 1 column "Emp Name 1" and select Query 1 and select column "Emp Name 1")
10) Merge queries again (Select Append 1 column "Emp Name 1" and select Query 2 and select column "Emp Name 1")
11) Remove excess columns ("Emp Name 1.2" and "Emp Name 1.1")
12) On "Utilisation" column Replace Values (Value to Find: null, Replace with: 0)
13) On "Emp 2 Utilisation" column Replace Values (Value to Find: null, Replace with: 0)
14) Add Column Tab: Custom column
15) New Column Name: Total Utilization
16) Custom column formula: [Utilisation]+[Emp 2 Utilisation]
Change to % and you should be set
Quite a few steps so if someone has a quicker more efficient way I'd be interested to know