Forum Discussion

Serj's avatar
Serj
Frequent Visitor
3 years ago
Solved

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 4...
Need Help
Show and Tell
  • Anonymous's avatar
    Anonymous
    3 years ago

    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

Anonymous's avatar
Anonymous
Not applicable
3 years ago

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