topic Addcolumn, different cases, summarize in DAX Commands and Tips

Addcolumn, different cases, summarize

Foguete — Tue, 24 Jan 2023 14:39:54 GMT

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

Re: Addcolumn, different cases, summarize

FreemanZ — Tue, 24 Jan 2023 15:14:54 GMT

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:

Re: Addcolumn, different cases, summarize

FreemanZ — Tue, 24 Jan 2023 15:22:26 GMT

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

Re: Addcolumn, different cases, summarize

Foguete — Tue, 24 Jan 2023 15:49:44 GMT

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 ?

Re: Addcolumn, different cases, summarize

FreemanZ — Tue, 24 Jan 2023 15:57:59 GMT

hi Foguete

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

Re: Addcolumn, different cases, summarize

Foguete — Tue, 24 Jan 2023 16:01:39 GMT

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)

Re: Addcolumn, different cases, summarize

FreemanZ — Tue, 24 Jan 2023 16:11:08 GMT

Brilliant! Great idea!