Addcolumn, different cases, summarize

[Foguete]

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

[FreemanZ · Accepted solution]

hi @Foguete 

try to create a calculated table like:

Table = 
VAR _table= 
ADDCOLUMNS(
    SUMMARIZE(
        TableName,
        TableName[Date],
        TableName[task]
    ),
    "center", "c",
    "Qty", CALCULATE(SUM(TableName[Qty]))
)
RETURN
UNION(TableName, _table)

verified and worked like this:

[Foguete]

Thank you @FreemanZ , however I have been simplifying "TableName" and in reality it has more column. So on, UNION is throwing the error "it must have the same number of columns".

How can I get through that ?

[FreemanZ]

hi @Foguete 

i am afraid there might be no easy workaround. How many column do you have?

[Foguete]

Hi @FreemanZ,

Thanks for the DAX, a slight modification and I have found the solution.

Here in red, it is in case someone would get there:

UNION(

    SELECTCOLUMNS(

        TableName,

            "date", TableName[date],

            "task", TableName[task],

            "center", TableName[center],

            "qty", TableName[qty]

    ),

    _table)

[FreemanZ]

Brilliant! Great idea!

[FreemanZ]

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]
)