cancel
Showing results for
Did you mean:
Regular Visitor

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

1 ACCEPTED SOLUTION

Accepted Solutions
Super User

Re: Remove duplicated rows in SUM calculation

OK, try this:

Measure = SUMX(SUMMARIZE(DistinctSum,[KEY],"Payment",AVERAGE(DistinctSum[PAYMENT])),[Payment])

I have book! Learn Power BI from Packt

Proud to be a Datanaut!

9 REPLIES 9
Super User

Re: Remove duplicated rows in SUM calculation

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])

I have book! Learn Power BI from Packt

Proud to be a Datanaut!

Super User

Re: Remove duplicated rows in SUM calculation

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] )
)
Try my new Power BI game Cross the River
Super User

Re: Remove duplicated rows in SUM calculation

@johnf

See the attached file here

Try my new Power BI game Cross the River
Regular Visitor

Re: Remove duplicated rows in SUM calculation

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.

Super User

Re: Remove duplicated rows in SUM calculation

Not sure, I recreated your table exactly, can you post your formula?

I have book! Learn Power BI from Packt

Proud to be a Datanaut!

Regular Visitor

Re: Remove duplicated rows in SUM calculation

@Greg_Deckler

I think this is because when passing the DISTINCT there's no condition applied to provide a boolean value for FILTER to use.

Super User

Re: Remove duplicated rows in SUM calculation

OK, try this:

Measure = SUMX(SUMMARIZE(DistinctSum,[KEY],"Payment",AVERAGE(DistinctSum[PAYMENT])),[Payment])

I have book! Learn Power BI from Packt

Proud to be a Datanaut!

Regular Visitor

Re: Remove duplicated rows in SUM calculation

HI,

I tried to make your stuff, but i dont know why, it's did the average of the whole column.

i have in the first table some times but some are for the same merged cells. In the second column i did the sum betwin cells which have the same indicator (merged) so in the list there is for example

12    12   Q125

15    27    Q158

14    14    Q789

12    27   Q158

14    14   Q963

And I need to have just

12    12   Q125

15    27    Q158

14    14    Q789

14    14   Q963

New Table =
SUMMARIZE (
Table_owssvr4;
Table_owssvr4[Merged.1];
"total fg "; AVERAGE ( 'Table 4'[total tempo FG])
)

Can you help me?

Highlighted
Regular Visitor

Re: Remove duplicated rows in SUM calculation

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.

Announcements

Kudos to you if you earned one of these! Check your inbox for a notification.

Microsoft Implementation for Communities Wins Award

Learn about the award-winning innovation that was implemented across Microsoft’s Business Applications Communities.

Power Platform World Tour

Find out where you can attend!

Top Solution Authors
Top Kudoed Authors (Last 30 Days)
Users online (3,702)