Sum all except selected value

[CarlsBerg999]

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?

[v-robertq-msft · Accepted solution]

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.

[amitchandak]

@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