Forum Discussion
SUMX with duplicate values
Hi all,
I've created a SUMX formula as per below which brings back values in a column which are minus:
MINUS = SUMX (
( VALUES ( TotalData[ Cost] )),
IF ( TotalData[ Cost] < 0, TotalData[ Cost], BLANK () )
)
However if there are duplicate values in the column it doesn't count them, for example I have one filter which has the following values:
-102.8933
-74.8
-76.02
-85.566
-85.566
-27.88
-35.52
Obviously that should bring back -488.2453 however it brings back -402.6793 because it's removing one of the -85.566 records.
Any ideas?
Thanks,
Mark
Hi Anonymous
you dont need VALUES here.
just table name or ALL()
MINUS = SUMX ( TotalData, IF ( TotalData[ Cost] < 0, TotalData[ Cost], BLANK () ) )or
MINUS = SUMX ( ALL(TotalData), IF ( TotalData[ Cost] < 0, TotalData[ Cost], BLANK () ) )
4 Replies
Anonymous : VALUES function will always return unique list of values, so it would be better if you can remove the Values in SUMX function
Hi Anonymous
you dont need VALUES here.
just table name or ALL()
MINUS = SUMX ( TotalData, IF ( TotalData[ Cost] < 0, TotalData[ Cost], BLANK () ) )or
MINUS = SUMX ( ALL(TotalData), IF ( TotalData[ Cost] < 0, TotalData[ Cost], BLANK () ) )az38 Works a treat, thanks!
VALUES only returns distinct values. I don't know why you are using VALUES there, it seems to be rampant that people throw VALUE or VALUES into formulas when they are not needed. I wish I could find the source of where that bad habit comes from. Oh well.
Try this:
MINUS = SUMX ( FILTER('TotalDate',[ Cost] < 0), [ Cost] )Added bonus, should be way more performant.
