Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hi guys,
I have 2 tables:
1. Available Hours across Teams/Roles for Week1 – Week3
Team | Role | Week1 | Week2 | Week3 |
Red | A | 40 | 40 | 40 |
Red | B | 40 | 40 | 40 |
Red | C | 40 | 40 | 40 |
Blue | A | 40 | 40 | 40 |
Blue | D | 40 | 40 | 40 |
2. Required Hours allocation:
Team | Role | Week1 | Week2 | Week3 |
Red | A | -20 | -20 | -20 |
Blue | A | -40 | -40 | -40 |
Blue | D | -10 | -10 | -10 |
Green | A | -20 | -20 | -20 |
What I want to get is an Hours Balance table calculated as Available Hours - Required Hours :
Team | Role | Week1 | Week2 | Week3 |
Red | A | 20 | 20 | 20 |
Red | B | 40 | 40 | 40 |
Red | C | 40 | 40 | 40 |
Blue | A | 0 | 0 | 0 |
Blue | D | 30 | 30 | 30 |
Green | A | -20 | -20 | -20 |
What I did was append these two tables with Available Hours and Required Hours, however, the output table does not summarize calculations and shows each entry as a separate row:
Team | Role | Week1 | Week2 | Week3 |
Red | A | 40 | 40 | 40 |
Red | A | -20 | -20 | -20 |
Red | B | 40 | 40 | 40 |
Red | C | 40 | 40 | 40 |
Blue | A | 40 | 40 | 40 |
Blue | A | -40 | -40 | -40 |
Blue | D | 40 | 40 | 40 |
Blue | D | -10 | -10 | -10 |
Green | A | -20 | -20 | -20 |
Could you pls help me to get the described desired Hours Balance table.
Thank you! Have a great day!
Solved! Go to Solution.
Hi @Serj ,
Here are the steps you can follow:
1. In Power Query – Home – Append Queries -- Append Queries as new.
Result:
2. Create calculated table.
True =
SUMMARIZE(
'Append1',
'Append1'[Team],'Append1'[Role], "Week1",SUMX(FILTER(ALL('Append1'),'Append1'[Team]=EARLIER('Append1'[Team])&&'Append1'[Role]=EARLIER('Append1'[Role])),[Week1]), "Week2",SUMX(FILTER(ALL('Append1'),'Append1'[Team]=EARLIER('Append1'[Team])&&'Append1'[Role]=EARLIER('Append1'[Role])),[Week2]), "Week3",SUMX(FILTER(ALL('Append1'),'Append1'[Team]=EARLIER('Append1'[Team])&&'Append1'[Role]=EARLIER('Append1'[Role])),[Week3]))
3. Result:
Best Regards,
Liu Yang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly
Hi @Serj ,
Here are the steps you can follow:
1. In Power Query – Home – Append Queries -- Append Queries as new.
Result:
2. Create calculated table.
True =
SUMMARIZE(
'Append1',
'Append1'[Team],'Append1'[Role], "Week1",SUMX(FILTER(ALL('Append1'),'Append1'[Team]=EARLIER('Append1'[Team])&&'Append1'[Role]=EARLIER('Append1'[Role])),[Week1]), "Week2",SUMX(FILTER(ALL('Append1'),'Append1'[Team]=EARLIER('Append1'[Team])&&'Append1'[Role]=EARLIER('Append1'[Role])),[Week2]), "Week3",SUMX(FILTER(ALL('Append1'),'Append1'[Team]=EARLIER('Append1'[Team])&&'Append1'[Role]=EARLIER('Append1'[Role])),[Week3]))
3. Result:
Best Regards,
Liu Yang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
64 |
User | Count |
---|---|
135 | |
120 | |
101 | |
71 | |
61 |