Forum Discussion
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
- Anonymous7 years agoNot applicable
thanks Bridgett
quite a lot of steps. I'll create a test copy of my Pbix and give it a try.
thanks
Adam