Remove duplicated rows in SUM calculation

[johnf]

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

[tabuzahra]

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.

[Greg_Deckler]

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

[johnf]

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.

[Greg_Deckler]

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

[johnf]

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

[Greg_Deckler · Accepted solution]

OK, try this:

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

[aschillinger20]

@Greg_Deckler This seems like a working answer for an issue i'm having except i dont see the option for "DistinctSum". My data structured like this: 

Order Number |  Hours 

123544             |      2

123544             |      2

178113             |      1

199911             |      5

I would like to remove the duplicate 123544 from my sum to see 8 as the total instead of 10. 

[Zubair_Muhammad]

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

[Zubair_Muhammad]

@johnf

See the attached file here

(With your sample data)

[kartikkaran]

Hi @Zubair_Muhammad  and @Greg_Deckler 

I have a similar issue with two columns "Engagement" and "Size" - tried the below formula but it gave an error "The expression refers to multiple columns. Multiple columns can not be converted to a scalar value".

1. Under the "Engagement" column, I have values as "Name A","Name B","Name A","Name B", "Name C","Name C","Name D","Name A","Name B","Name A" etc...
2. Under the "Size" column, I have values as "10","20","40","30","50","70","60","10","50","40"

1 Enrollment = SUMMARIZE('Demographic Information','Demographic Information'[Engagement], "Participants", AVERAGE('Demographic Information'[Size]))

[MilanAXVII]

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?