How to combine duplicate rows and sum its values

[Maggi029]

How to combine duplicate rows and sum its values

Example of my table

Category	Items	Items Count	Sell Date
Fruits	Apple	3	29/10/2021
Fruits	Apple	2	29/10/2021
Vegetable	Carrot	3	29/10/2021
Vegetable	Carrot	2	29/10/2021
Fruits	Apple	1	30/10/2021

I need to get my table like this:

Category	Items	Items Count	Sell Date
Fruits	Apple	5	29/10/2021
Vegetable	Carrot	5	29/10/2021
Fruits	Apple	1	30/10/2021

How i can i acheive this?

[Samarth_18]

Hi @Maggi029 ,

You can achive this by below two ways:-

1. Drag your column on table visual and use item count as sum 

2. Or You can create a custom table with below code:-

Table 2 =
SUMMARIZE (
    'Table',
    'Table'[Category],
    'Table'[Items],
    'Table'[Sell Date],
    "sum", SUM ( 'Table'[Items Count] )
)

Thanks,

Samarth

[Maggi029]

Hi @Samarth_18 

Thanks for your response; it appears to be satisfactory; however, I must continue to use the old table in my project, as this will increase my pbix weight. My main goal is to reduce my pbix size, and to do so, I must trim down the table.

[Samarth_18 · Accepted solution]

Oh okay, In that case you can follow below steps:-

1. Click on group by option in edit query window:-

 

2. Then select column as below and click OK:-

3, You will see below output and then click on close and apply:-

[Maggi029]

Hi @Samarth_18 

Thanks you for your answer

[Anonymous]

Hello @Maggi029 

You can create a simple measure and plot in the table/matrix.

Total Items = SUM( 'Table'[Items Count] )

[Maggi029]

Hi @Anonymous 

No instead of measure, i need to trim down my table rows count, for i need to combine my rows