cancel
Showing results for
Did you mean:
Highlighted
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 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.

2 REPLIES 2
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

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