Forum Discussion
Summarization inside appended table
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!
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
1 Reply
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