Group by, Removing Duplicates, and Adding Rows

[pbiwizard]

Hi! 

I am looking for help with removing duplicates when grouping, and adding a column. Or however, I can achieve the second table (i just started off with group-by and summing but that did not work because to double counts my rows). 

Here is an example: 

Key ID	Gross Sales	Exempt Sales	NonTaxed Sales	Amount Subject to Tax	Sales Tax	Rate
00001	100.00	0	0	100.00	6.00	6%
00001	100.00	0	0	100.00	1.00	1%
00001	100.00	0	0	100.00	0.50	0.5%
00001	100.00	0	0	100.00	2.00	2%
00002	50.00	0	50.00	0	0	0
00002	20.00	0	0	20.00	10.00	50%
00003	75.00	75.00	0	0	0	0
00004	30.00	0	30.00	0	0	0
00004	10.00	0	0	10.00	1.00	10%
00004	30.00	0	30.00	0	0	0
00004	10.00	0	0	10.00	2.00	20%

Above we have instances where I there are duplicates in several columns. However, the driving factors are the Gross Sales and the Rate, along with the transaction ID. However, since there are different sales taxes depending on jurisdiction, the rows repeat for the transaction (even though they are blank since the exempt and non-taxable amount drive the rate (if there is something in this column then the rate is 0). 

This is what I would like the column to look like. 

Key ID	Gross Sales	Exempt Sales	NonTaxed Sales	Amount Subject to Tax	Sales Tax	Rate
00001	100.00	0	0	100.00	9.50	7.5%
00002	70.00	0	50.00	20.00	10.00	50%
00003	75.00	75.00	0	0	0	0
00004	40.00	0	30.00	10.00	3.00	30%

I would like one row per key. If the values in each colum (per key) I would like them to be added, other wise they will be double counted and that is incorrect. So if the values are different, I would like them to be added. 

Thank you! 

[parry2k · Accepted solution]

@pbiwizard You don't need to create a new table, just add measure for each column and then use that in the visual, here is the example of the measure for Gross Sales

Gross Sales M = 
SUMX ( 
    SUMMARIZE ( TableSum, TableSum[Key ID], TableSum[Gross Sales] ), 
    [Gross Sales] 
)