Forum Discussion
Remove duplicated rows in SUM calculation
Hello,
I'm having some issues in trying to work out how to remove duplicate rows from a calculation.
I have the following dataset (highly simplified from actual)
| KEY | NAME | JOB | PAYMENT |
| Bill|100 | Bill | Electrician | 100 |
| Bill|100 | Bill | Plumber | 100 |
| Jenny|200 | Jenny | Electrician | 200 |
| Simon|500 | Simon | Plumber | 500 |
| Simon|500 | Simon | Gardner | 500 |
The issue is that the system the data imports from the payment calculation is for the total of all jobs carried out by that person. For example, Bill did work as an Electrician and a Plumber but was only paid a total of 100 for both not the summation of both job payments.
To attempt to filter out these duplications, I have created concatenated keys so that I should be able to filter the duplicates and sum all payments without double counting. So in the example above the total payments made to all workers should only be 800 but I can't seem to get the DAX right to remove the duplications. Can anyone assist please?
Thanks,
John
OK, try this:
Measure = SUMX(SUMMARIZE(DistinctSum,[KEY],"Payment",AVERAGE(DistinctSum[PAYMENT])),[Payment])
11 Replies
What about removing the duplicates in Power Query instead?
In DAX, I would use SUMX with a DISTINCT:
https://msdn.microsoft.com/en-us/library/ee634943.aspx
Measure = SUMX(FILTER(Table,DISTINCT(Table[KEY])),Table[PAYMENT])
Hi johnf
Another way of doing it
Go to Modelling Tab >>>NEW TABLE and use this formula
New Table = SUMMARIZE ( TableName, TableName[NAME], "Job", CONCATENATEX ( TableName, TableName[JOB], "," ), "Payment", AVERAGE ( TableName[PAYMENT] ) )Thanks Greg_Deckler.
I had thought along the same lines, but for some reason in trying this formula I get the "A table of multiple values was supplied where a single value was expected" error.
I think this is because when passing the DISTINCT there's no condition applied to provide a boolean value for FILTER to use.
As far as using Power Query to remove the duplicates, unfortunately, I need the other detail for other calculations. e.g. In this example show total paid to Plumbers.
Not sure, I recreated your table exactly, can you post your formula?
- Hello,
How can you sum two values from two different tables? I have tried to use it as a measure but the thing is that it duplicates the rows.
