Sunnarizing data

[Anonymous]

Dear all,

I have a small problem which almost breaks my nerves (after 2 hours searching for a solution).

I have a table which looks like this:

ITEMNUMBER	DESCRIPTION	OURREF	Status_order	Quantity
AMS2000-002	ML2i USB Host Adapter ''FULL''	C190005	Ready for Invoicing	1
AMS2000-002	ML2i USB Host Adapter ''FULL''	C190005	Ready for Invoicing	2
AMS2000-002	ML2i USB Host Adapter ''FULL''	C190005	Ready for Invoicing	3
AMS2000-002	ML2i USB Host Adapter ''FULL''	C190005	Ready for Invoicing	4
AMS2000-002	ML2i USB Host Adapter ''FULL''	C190005	Ready for Invoicing	5
AMS2000-002	ML2i USB Host Adapter ''FULL''	S190470	Closed	5
AMS2000-002	ML2i USB Host Adapter ''FULL''	S190179	Closed	4
AMS2000-002	ML2i USB Host Adapter ''FULL''	S190517	Quotation	3
AMS2000-002	ML2i USB Host Adapter ''FULL''	S190471	Quotation	6
AMS2000-002	ML2i USB Host Adapter ''FULL''	S190432	Await Payment	7
AMS2000-002	ML2i USB Host Adapter ''FULL''	S190380	Confimed	8
AMS2000-002	ML2i USB Host Adapter ''FULL''	S190059	Confirmed	1
AMS2000-002	ML2i USB Host Adapter ''FULL''	S190059	Closed	2

I am looking for the proper DAX solution to summarize this table to the below table.

Your help is highly appreciated!

John

ITEMNUMBER	DESCRIPTION	OURREF	Status_order	Quantity
AMS2000-002	ML2i USB Host Adapter ''FULL''	C190005	Ready for Invoicing	15
AMS2000-002	ML2i USB Host Adapter ''FULL''	S190470	Closed	9
AMS2000-002	ML2i USB Host Adapter ''FULL''	S190517	Quotation	9
AMS2000-002	ML2i USB Host Adapter ''FULL''	S190432	Await Payment	7
AMS2000-002	ML2i USB Host Adapter ''FULL''	S190380	Confimed	9
AMS2000-002	ML2i USB Host Adapter ''FULL''	S190059	Closed	2

[Ashish_Mathur · Accepted solution]

Hi,

Drag columns1,2 and 4 to the Table visual and write this measure

=SUM(Data[Quantity])

Hope this helps.

[Anonymous]

Sorry, in the summary table, I do not need the "OURREF" column

[HotChilli]

Table = SUMMARIZECOLUMNS(Table1[ITEMNUMBER], Table1[DESCRIPTION], Table1[Status_order], "Quantity", SUM(Table1[Quantity]))

If I can assume that there is a slight mistake in your desired table (the extra 'Closed' status line)