cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
johnf Regular Visitor
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)

 

KEYNAMEJOBPAYMENT
Bill|100BillElectrician100
Bill|100BillPlumber100
Jenny|200JennyElectrician200
Simon|500SimonPlumber500
Simon|500SimonGardner500

 

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


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

View solution in original post

9 REPLIES 9
Super User
Super User

Re: Remove duplicated rows in SUM calculation

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

I have book! Learn Power BI from Packt


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

Super User
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
Super User

Re: Remove duplicated rows in SUM calculation

@johnf

 

See the attached file here

(With your sample data)

 

3000.png

Try my new Power BI game Cross the River
johnf Regular Visitor
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
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


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

johnf Regular Visitor
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
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


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

View solution in original post

MilanAXVII Regular Visitor
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.new table.PNG

 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
tabuzahra Regular Visitor
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.

Helpful resources

Announcements
New Kudos Received Badges Coming

New Kudos Received Badges Coming

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

Microsoft Implementation for Communities Wins Award

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

Power Platform World Tour

Find out where you can attend!

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