Forum Discussion
Remove duplicated rows in SUM calculation
OK, try this:
Measure = SUMX(SUMMARIZE(DistinctSum,[KEY],"Payment",AVERAGE(DistinctSum[PAYMENT])),[Payment])
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] ) )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?
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?
I think this is because when passing the DISTINCT there's no condition applied to provide a boolean value for FILTER to use.
