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])
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.
OK, try this:
Measure = SUMX(SUMMARIZE(DistinctSum,[KEY],"Payment",AVERAGE(DistinctSum[PAYMENT])),[Payment])