Forum Discussion
Solved
Addcolumn, different cases, summarize
Hello,
I need help as I am trying to build a table with a case I never face untill now.
I currently have a table with different line that I want to summarize.
For example:
| date | task | center | qty |
| 01/01/2023 | 1 | a | 2 |
| 01/01/2023 | 2 | a | 3 |
| 02/01/2023 | 2 | a | 4 |
| 02/01/2023 | 2 | b | 5 |
| 02/01/2023 | 1 | a | 1 |
| 03/03/2023 | 1 | b | 2 |
| 03/03/2023 | 2 | a | 3 |
| 03/03/2023 | 1 | b | 4 |
| 03/03/2023 | 2 | a | 5 |
I want to SUMMARIZE by date, task, SUM(qty) BUT in the new table , I want to add a variable to "center", let's say C, which would be center A and B together.
So on the result would looks like this:
date | center | task | qty |
| 01/01/2023 | a | 1 | 2 |
| 01/01/2023 | a | 2 | 3 |
| 01/01/2023 | c | 1 | 2 |
| 01/01/2023 | c | 2 | 3 |
| 02/01/2023 | a | 2 | 4 |
| 02/01/2023 | b | 2 | 5 |
| 02/01/2023 | a | 1 | 1 |
| 02/01/2023 | c | 2 | 9 |
| 02/01/2023 | c | 1 | 1 |
| 03/03/2023 | b | 1 | 6 |
| 03/03/2023 | a | 2 | 8 |
| 03/03/2023 | c | 1 | 6 |
| 03/03/2023 | c | 2 | 8 |
Any help regarding a function or anything I could use ?
Kind regards
6 Replies
hi Foguete
if you mind the column ordering, try this:
Table = VAR _table= ADDCOLUMNS( SUMMARIZE( TableName, TableName[Date], TableName[task] ), "center", "c", "Qty", CALCULATE(SUM(TableName[Qty])) ) VAR _table2= UNION(TableName, _table) RETURN SELECTCOLUMNS( _table2, "date", TableName[date], "center", TableName[center], "task", TableName[task], "qty", TableName[qty] )
