cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Adam_Defries Frequent Visitor
Frequent Visitor

Calculation between tables

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
CustomerUtilisatonEmp Name 1Emp Name 2
Cust 110%Joe Bloggs 
Cust 215%Jane BigsJoe Bloggs
Cust 310%Joe Bloggs 
Cust 420%Jane BigsJoe Bloggs
Cust 510%Jane Bigs 
Cust 615%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 Name1UtilizationEmp 2 UtilizationTotal Utilization
Joe Bloggs20%35%55%
Jane Bigs60%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 name2Utilzation
Joe Bloggs35%
  

 

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. 

2 REPLIES 2
Bridgett Frequent Visitor
Frequent Visitor

Re: Calculation between tables

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

Highlighted
Adam_Defries Frequent Visitor
Frequent Visitor

Re: Calculation between tables

thanks Bridgett

 

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

 

thanks
Adam