Merge Rows based on ID column and Sum number Values in Third column

[lkshck]

Hey,

I have the following Table visual and I want to merge the rows based on the Column "PartNumber" as this one contains a lot of duplicates. In addition the Column "Sum of Cost" should calculate the Total based on the "PartNumber". 

Product	PartNumber	Sum of Cost
Virtual Network Peering	AAD-12345	500,00€
Virtual Network Peering	AAD-12345	256,66€
Virtual Network Peering	AAD-12345	25,99€
Event Hubs	AAB-6789	600,00€
Event Hubs	AAB-6789	123,45€
IoT Hub	AAA-45678	800,00€
IoT Hub	AAA-45678	799,99€

Is there any way to do it directly via the Table visual?

Thanks in advance!

[lkshck · Accepted solution]

Got it solved as I added more columns with additional data to the table and those columns were defined as "Don't group". Underlying data is based on daily data, this was the reason it wasn't group. So topic can be closed.

[jaweher899]

Yes, you can do it directly in the Table visual. To merge rows based on the Column "PartNumber" and sum the values of the "Sum of Cost" column, you can follow these steps:

1. Create a calculated column to sum the values of the "Sum of Cost" column, using the formula =SUMX(GROUPBY(TableName, TableName[PartNumber]),TableName[Sum of Cost]). Replace "TableName" with the name of your table.

2. Create a new table visual by going to "Modeling" in the ribbon, then selecting "New Table" and "Blank Table".

3. In the new table, add the columns "Product" and "PartNumber" from the original table, and the calculated column created in step 1.

4. Group the new table by the "PartNumber" column.

5. Delete the original table visual and replace it with the new table visual.

Now you will have a table with the merged rows based on the "PartNumber" column and the calculated "Sum of Cost" values.

[lkshck]

Thanks for providing this solution. Do you know if this works also for a Report which has a Live connection to an already published dataset? As the underlying is very large and complex I created a simple report which is jused based on this dataset, to not duplicate the complete dataset.