Forum Discussion
Sum all except selected value
Hi,
I'm having issues calculating a simple sum for all values except for the selected value. For some reason the DAX formula only accepts the sum for the selected value but not for the opposite:
This works:
CALCULATE(SUM(['Table1' [Column1]),
FILTER(Table1,Table1[Column2]=SELECTEDVALUE(Table2[Column4])
This doesn't work:
CALCULATE(SUM(['Table1' [Column1]),
FILTER(Table1,Table1[Column2]<>SELECTEDVALUE(Table2[Column4])
Why is this and how to fix this?
Hi, CarlsBerg999
According to your description, I think if the second measure can work is based on the relationship between your ‘Table1’ and ‘Table2’, if they have a relationship, then I think you can change the DAX formula of your second measure like this to make it work:
Measure= CALCULATE(SUM(['Table1' [Column1]), FILTER(ALL(Table1),Table1[Column2]<>SELECTEDVALUE(Table2[Column4])More info about the ALL() function in DAX
If they don’t have a relationship, I think both the two measures can normally work.
If this result is not what you want, you can post some sample data(without sensitive data) and your expected result.
How to Get Your Question Answered Quickly
Thank you very much!
Best Regards,
Community Support Team _Robert Qin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
2 Replies
CarlsBerg999 , If Table1 and Table2 are not connected both should work
Or try like
CALCULATE(SUM(['Table1' [Column1]),FILTER(all(Table1),Table1[Column2]<>SELECTEDVALUE(Table2[Column4])))
check the diff in such calculation with and without independent table
Need of an Independent Table in Power BI: https://youtu.be/lOEW-YUrAbE
Hi, CarlsBerg999
According to your description, I think if the second measure can work is based on the relationship between your ‘Table1’ and ‘Table2’, if they have a relationship, then I think you can change the DAX formula of your second measure like this to make it work:
Measure= CALCULATE(SUM(['Table1' [Column1]), FILTER(ALL(Table1),Table1[Column2]<>SELECTEDVALUE(Table2[Column4])More info about the ALL() function in DAX
If they don’t have a relationship, I think both the two measures can normally work.
If this result is not what you want, you can post some sample data(without sensitive data) and your expected result.
How to Get Your Question Answered Quickly
Thank you very much!
Best Regards,
Community Support Team _Robert Qin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
