Calculation between tables

[Anonymous]

Hi All

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
Joe Bloggs	20%	35%	55%
Jane Bigs	60%	0%	60%

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
Emp name2	Utilzation
Joe Bloggs	35%

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. 

[Bridgett]

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)
😎 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

[Anonymous]

thanks Bridgett

quite a lot of steps. I'll create a test copy of my Pbix and give it a try. 

thanks
Adam