Forum Discussion

Icon for Helper I rank

Helper I

8 years ago

Solved

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

8 years ago

OK, try this:

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

Icon for Community Champion rank

Community Champion

8 years ago

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

Zubair_Muhammad

Icon for Community Champion rank

Community Champion

8 years ago

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
Community Champion
8 years ago
johnf

See the attached file here
(With your sample data)
- MilanAXVII
  Helper I
  8 years ago
  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?
- kartikkaran
  Regular Visitor
  5 years ago
  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".
  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...
  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]))

Related content